Collaborate on Hourly Invoice Template Google Docs for Product Management with Ease Using airSlate SignNow

Hourly invoice template google docs for Product Management

hello and welcome to this video today I'm going to teach you how to create this Dynamic calendar from scratch literally starting with a blank spreadsheet and the way this works is you're going to be able to add events into this table and then they will appear on their corresponding day in the calendar for example if I wanted to add something on May 28th I would just type in something new and then it pops up here automatically and I know it's been a while since I made a tutorial I haven't forgotten about them I still love making them I was just busy creating my website so that's ready I made it all myself so it took me a while to just be happy with the look so that is live and if you would like to check it out I would be really grateful so I'm Gonna Leave the link to that in the description and you can also find the link to this final file in the description down below so let's get into it whenever you're following one of my tutorials I always suggest that you do everything exactly in the same cells that I'm doing it just so if you have any questions you run into any dishes you get stuck somewhere our formulas will look exactly the same and your formulas will look exactly the same as the example file so you will be able to compare so the very first thing we're going to create is the calendar area so I'm going to start by selecting cell F4 and you can always see which cell is selected up here and then I'm going to click on data data validation and Rule and then from this criteria option I'm going to select the option is valid date I'm going to click on reject the input and then I'm going to click on done and what this does is it attaches a tiny calendar to my cell from which I can select a date now this is my very first day and I need the other six days and that's really simple I'm just gonna do equals I'm going to select my first date and then plus one and that's giving me the next day so now I'm just going to click and drag this to apply this same formula to the next five cells so one two three four and five and that gives me seven days the goal is for these days to look like this so I'm going to go ahead I'm going to select all of this and I'm going to click on format number custom date and time so personally this is the one that I'm gonna use but you have plenty of options if something else looks better to you so I'm going to click there and now my dates look like this now usually I like every everything to be centered from the beginning while I'm working so I'm going to go ahead and I'm gonna click on this square right here and that's going to select the entire sheet so I'm going to come into this alignment options and I'm going to click in here and I'm going to Center and then I'm going to click in here and I'm gonna align it in the middle and then I'm Gonna Leave This wrap option selected so now that it's all centered I'm going to go ahead and I'm going to skip one two three four five and six and in this row I'm gonna input the formula equals I'm going to select this very first day up here and then I'm gonna do plus seven so it's pretty straightforward I'm just adding seven days so I can get the next day after this one of course you could also go ahead and select this one and do plus one but for this tutorial I'm going to use this method which I'm going to select this one plus seven and as you can see we go from June 3rd to June 4th and then I'm just gonna click and I'm gonna drag the formula all the way over here and then I'm just gonna give it a little bit of formatting not too much just some borders so I'm gonna select this area from cell F4 to cell L9 and I'm just gonna give it a black border and then I'm going to select this top area right here and I'm going to give it a border and I'm going to show you a little trick so to apply this same formatting to this area down below I'm going to select again the range from cell F4 to cell L9 and then I'm going to click on this paint format button right next to the printer so I'm going to click it and as you can see it turned blue so immediately after that I'm going to go ahead and I'm going to click on this day right here which is cell F10 and once I do that the formatting was copied to that group of cells down below which is extremely useful now since everything down below the rest of the four weeks that we're gonna add are going to be exactly the same I'm not going to work on them just yet because it's basically copying and pasting so we're gonna leave this as it is and then we're gonna move on to creating these labels up here so the way this works is whenever you select a date from the calendar the entire calendar gets updated and then the days at the top get updated as well so as you can see if I select a Friday Friday June 16th then this becomes Friday so let's create that and it is super simple all you're going to do is you're going to come into cell F2 and then you're going to do equals you're going to select this cell down below enter and then you're gonna click and you're gonna drag and basically what you're doing is just you're repeating the exact same date that you're seeing down here but the trick is you're gonna select this area and then you're going to click on format number you're gonna go into custom date and time and you're going to find the option that shows you the day as a full name or you can also use the day as an abbreviation so you can scroll and you're going to find it somewhere around here so you're going to select that option you're going to click apply and as you can see these cells which are actually just hidden dates behind the scenes are showing you their weekday so I always like to do this you know my templates it's super fun and super easy so now you're just gonna double click and play around and make sure your calendar is updating automatically so pause the video and just make sure that everything in your file up to this point is working perfectly so if you're selecting a Wednesday make sure this becomes Wednesday and then all updates are consecutive weekdays and then from this day to this day you should be increasing one so make sure everything looks okay and then let's move on so I'm gonna leave it like this for now and I'm gonna go ahead and I'm gonna create this list right here I'm gonna bring this back to May 28th so you still have all of your tasks in the example now the very first thing I'm gonna do is I'm going to make this two columns smaller I'm a perfectionist and I like everything to be really symmetrical so what I like to do is I'm going to select them both at the same time and the way I did that is I clicked on one of them on the column letter and then I press command on my computer and then I clicked on the other one so I'm not exactly sure what that option would be on a Windows you can just go ahead and Google that if anybody here knows what that is on Windows please comment down below and once I have those selected I'm just gonna click and I'm gonna drag to make them smaller but the exact same size now I'm going to go ahead and I'm going to set my labels up here so this is going to be the number this is going to be this and this is going to be the task now the number doesn't have to be that wide so I'm just going to make this smaller I'm gonna make gonna make smaller and then this one I might want it to be a bit larger the next thing I'm going to do is I'm going to create a sequence of numbers so I'm going to go with 50. so I'm gonna go into cell B4 and I'm gonna do equals sequence and then I'm going to input 50 because I want 50 rows I want one column I want to start in number one and I want to go in increments of one so those are the options that I'm filling out and then I'm just going to close parentheses if you want more rows more numbers you can just change this number but I suggest for the sake of the tutorial you just do everything exactly the way I'm doing it so then I'm going to press enter and I have a sequence of 50 numbers so that's as far as my table is going to go for the tutorial as you may have noticed because I just noticed it myself I actually only added five rows here and the example has six rows so we're just in time to fix that I'm sorry you're very quickly just gonna go into row six you're gonna select it you're gonna right click and insert one row above and then you're gonna do the same thing down below on row 13 maybe just somewhere in the middle 13 is fine right click and insert third one row above I could have left it with five rows but I really wanted to be exactly as this example so now that's fixed and we can move on so the next thing I'm gonna do is I'm gonna add the calendar feature to these cells so this tiny calendar that allows you to select a date so I'm just gonna go ahead and select everything from row C4 to row c53 I'm gonna click on data data validation we've done this before add rule is validate reject the input if it's not a validate and then done and that should give us this feature so go ahead and test that it works all the way to row 50. I always only test for the very last row and the very first row since I selected consecutive cells so now that we have that I'm going to add a couple of tasks on my very first day which is May 3rd in my case so I'm just gonna do May 3rd and May 3rd I want it to look like this just prettier so I'm gonna go ahead and I'm going to select that range again and then I'm going to click on format number and it's very likely that the one you used for the calendar is going to show up here as you're recently used so you can click on it and if not you can just go into custom date and time and find the one you want to use and then click on apply so once we have that we are going into the main feature of this tutorial and it's how do I get my tasks from here into the calendar automatically so let's add a couple of tasks let's do task one and task two we're going to start with this day right here and it's a bit of a long formula but I'm gonna break it down in pieces and I'm gonna explain each one so we're going to start by going into cell F5 and doing equals filter open parenthesis and we are going to select the range that we want to filter which is going to be everything from cell D4 to cell d53 so this is what you should be seeing up here once you have that selected you're going to do a coma and then you're going to add your condition so whenever this date matches this date I want you to give me the task so my condition will be this range everything from cell C4 to cell c53 and then equals and I'm going to select cell F4 so what I'm saying is filter this range and give me every task where this date matches this date and then I'm going to press enter and see how that looks it looks great now what happens whenever I don't have a task assigned to that day so I'm going to delete both of my tasks I get this ugly thing up here and we don't want that so the way I'm gonna get rid of that error message is by using the if error formula so you're going to go into your formula and then you're gonna position your cursor right after the equals and you're going to type in if ever and then you're going to open parentheses so make sure it looks exactly like this and then you're gonna go all the way to the end of the formula you're gonna add a comma and then you're gonna close parentheses and what I'm saying here is if this formula ends up throwing an error then do this and in this case I wanted to do nothing I want it to be empty so I'm just gonna leave it like that just to show you an example to have this make more sense instead of living in empty I could add a note that says no events today for example so instead of throwing an error it's going to tell you no events today but I actually prefer it to BMT so I'm just going to delete this and then enter it's looking good now as you can see there's only six rows in here so what happens if you add seven or eight tasks so I'm gonna go ahead I'm gonna select my very first day my third in this case I'm gonna copy that date and then I'm gonna select everything below until my eighth task and then I'm gonna paste so I'm basically just repeating the date so immediately this failed you're also going to go ahead and add consecutive numbers here as your task just for the example and this is still an error the reason you have an error in here is because this formula is resulting in eight rows but this row is already occupied and since it can't overwrite it it says array result was not expanded because it would overwrite data in f11 so I already have something in f11 and this just can't go on Beyond this cell so we don't want this error to appear what we actually want is to have our first six tasks showing the calendar so how do I get rid of this error so it shows me only my first six tasks I'm gonna go into the formula again and then I'm gonna position the cursor right after the equals symbol and I'm gonna do array underscore and the word constrain so just make sure it looks exactly like that array underscore constraint and then I'm gonna go to the very end of the formula I'm going to do a comma and then I'm going to constrain this to six rows comma and one column and then I'm gonna close parentheses and now my formula will go all the way to my sixth task and if there's something after that then it won't show it until I remove another task so in this case if I remove this one then I get all the way to the seventh one if I remove this one I get three four five six seven eight so that's looking pretty good but we're still missing one thing so I'm gonna switch a couple of these tasks over to the next day which is May 4th and so as you can see they're gonna disappear from May 3 so I have these three tasks on May 3 which they're showing up here properly and tasks six seven and eight should appear here if I were to copy this formula and paste it directly in here it's not working because we are missing one step now if you look into the second formula my ranges are getting shifted to the right so instead of having cell F4 over here it changed to cell G4 which I actually want that but this also changed and I don't want that I want all my formulas for every single cell in the calendar to be fixed to this range to column C and to column D but the moment I copied and pasted my formula it shifted to column e and column D so I'm gonna delete the formula on May 4. I'm gonna go back to make three and I'm going to fix the formula to this range and the way I do that is I'm gonna position my cursor somewhere on the this range anywhere is fine and then I'm gonna press F4 and what happens is I get this dollar sign right before the column and right before the row you can also do it manually by adding dollar sign before the column and before the row and then again here before the column and before the row so nothing really happens here but now if I copy that formula and I paste it over here it's working properly this range is fixed this wrench is fixed but this one is Shifting one cell to the right so if I continue doing that and then maybe I move this to make five then it's going to work now my borders are getting copied as well and that is bothering me so I'm just gonna add borders to everything so I'm going to select everything from cell F4 to cell F10 and then I'm just going to give it a black border and as I taught you before you can select this range and then you can click on this paint format and then you're going to select this entire range everything from cell G4 to cell L10 immediately after while this is activated and that's just gonna copy my borders then if I were to select this click on the paint format and then click here and get those borders applied down here as well so now that we know our formula is working we are going to apply that to everything that we have right here to every single day so we're going to start by copying it and then we're going to select this six rows and then we're gonna paste and then you can see the formula was applied everywhere in here this one still doesn't have a formula so we're just going to go ahead and we're going to copy all these seven first rows of each day so that's the range from F5 to L5 we're gonna copy that and then we're gonna go ahead and paste that on Cell F12 and that will apply it to each one of our days so right now pause the video test this out and make sure everything is working properly so what I would do to test it is you can go ahead double click and select your very first day and then add the number one one and then click and select your second day add the number two and once you have those two you can go ahead and select these four cells the range from cell C4 to D5 and then you can drag all the way down maybe until task 15 and then as you can see a number appears on each one of these areas and that's a consecutive number so it's all looking good you can just keep playing around with it and make sure that it's working properly so I'm going to go ahead and I'm going to start decorating it and I'm going to leave the calendar to the very end so first something I really like to do is I like the headers to just remain fixed up top so I'm going to go ahead and I'm going to click on the second row and then I'm gonna click on view freeze up to row two and that's just gonna freeze that up top and then I'm going to create a border around this area so I'm just going to select everything from cell B4 to sell d53 and then I'm going to add a black border to it and then I'm also going to select this headers up here and apply this all borders option to that and I'm gonna do the same thing up here so I'm going to select them all and then I'm going to apply all borders and now my header I want this to be purple so I already added a purple color this one hold on a second and I'm going to show you the hex value so you can go ahead if you want this exact color you can go into this custom area and click on the add custom color option and then you can add this hex value to this input area so you can pause the video and copy that if you want this exact shade so I'm gonna add that and I'm gonna give it a white font and then this one I want it to be like a dark I don't know if you can call this pink hold on and I'm going to show you the hex value so same thing you can just go ahead and copy that one if you like it and then I'm gonna give it a white font I'm Gonna Leave the calendar to the very end and then I'm gonna select this area and I have this trick instead of clicking and dragging to select something you can click on a cell and go all the way down to the very end of the range that you want to select and then if you press shift and then you click on that cell you're going to select that area of consecutive cells so that entire range without having to drag all the way down and then for this one I'm gonna use this light purple that's already there by default and then I'm also going to make this font my dark purple and I'm going to add a border around it now that I have it like this there's one thing that I always like to do and is get rid of all the extra grid lines so I'm gonna go ahead I'm gonna click on view show and I'm going to click on grade lines and that makes the grid lines disappear now I do want some grid lines but I want them to be lighter and I don't want them to be in here or in here so I'm going to start here again I'm going to select my very first cell of the range I'm going to scroll down I'm gonna press shift and then I'm going to select my last cell of the range you can also just click on drag just make sure you select this area from cell B4 to cell d53 and then I'm going to click on borders and I'm going to change the color to maybe a lighter gray I'm going to use this light gray three and then I'm going to select inner borders so if you select all borders you're going to get rid of the black one so just select inner borders and that's looking very pretty I'm actually gonna bring back my black border over here because I just got rid of it or you can just leave it like that if you like it so I'm just gonna select this range then I'm gonna go back to selecting a black border and then I'm gonna do outer borders so yeah it's looking pretty much like the example I'm actually gonna make this Boulder so I'm gonna select this and I'm gonna click on Bold and I'm gonna do the same thing for the weekdays and I'm gonna click on Bold now I'm going to make this row smaller I do like some a little bit of white over here but I want it smaller and then maybe this one I can make it a bit taller so we have our dates we have our tasks and now I'm going to format the calendar so it looks pretty and I'm only going to focus on one day I'm just gonna copy that format and paste it everywhere else so I'm gonna start by giving this a lighter background I'm gonna use this light magenta up here for the font color I'm gonna use the pink magenta whatever that I used before I'm gonna make it bold and then I'm going to add an their borders in here just so I have some lines but have them be a lighter color so I'm just going to select this area from cell F5 to cell F10 and then I'm going to click on borders I'm gonna change the color to the same grade that I used on this table so this one and then I'm gonna click on inner borders so now that this is looking good I'm just gonna apply that formatting to the rest of myself so I'm just gonna go ahead and I'm gonna select this range from cell F4 to cell F10 I'm gonna click on the paint format and then I'm going to select the rest of my days in that same week and the formatting should be applied now that I did that here I'm gonna go ahead and I'm gonna select this entire area I'm going to click on paint format and then I'm going to click on Cell f11 which has the first day of my second week and the formatting should be applied now we can create the rest of our calendar so the way this area behaves as you may recall is you're just adding seven days to this day up top so that's the way each of these cells work which means that if I just copy this area and I paste it here my next week will work exactly the same way so as you can see May 16 May 17 and if I do the same thing down here it's just gonna keep on working so I'm gonna add six weeks so I'm missing two this is week five and this is week six just for those months that actually jump into a sixth week in a calendar so once I did that it's looking pretty good I'm just gonna go ahead and change the font because I actually don't like this one so I'm just gonna select everything as your Miracle you can select everything by clicking on this square right here and today I'm gonna use the font later so that's looking better and now they start too narrow so I'm gonna select all seven columns by clicking on the letter F and then dragging all the way to letter L and then you can resize them all at once so it's looking good now let's run a test to make sure that it is all working properly so this is how we're gonna run the test we are going to double click and we're going to select the very first day and then we're gonna do the number one then we're gonna select the second day and we're gonna do number two so you have to use this exact day so if yours are not May 3rd and May 4th then use the ones that you have and then you're gonna select both of those tasks both the date and the task and you're gonna drag all the way down and the borders got messed up and I'm going to show you a little trick before we continue with the testing so the borders got messed up because it's trying to replicate this border up here so I have a trick to fix that and this is a bonus that you can totally skip you're gonna select this range from cell B4 to cell D4 and you're gonna have your border be the same as your inner border so you're going to have your top border become these light gray color so now if I select these two tasks and remember not to select the number just select the tasks and the date so if I select that that won't happen anymore but I still don't have a border up here which is not looking pretty so what you're going to do instead of adding that top border into this group of cells you're gonna add a bottom border to this group of cells up top so if you select this range from cell B3 to D3 and then you click on borders you select the black border and you give it a bottom border that is black you get your border that still looks pretty but if you were to select this and click on drag then your borders won't be messed up if you were to copy and paste then you won't get the borders as well so that's a neat trick so once we did that we're just going to select everything and we're going to scroll all the way to the very end and as you can see the same thing happened here I destroyed my border so you're gonna do the same thing and you're gonna add a top border to this range below so as you can see from cell b54 to d54 you're gonna add a top border to this so it looks like it's the bottom border of this row but it actually isn't so if you were to copy this and paste it somewhere else you won't get the border so I just I love that trick I hope you find it useful so now as you can see each day is getting a task so I copied my formulas properly and everything is looking great now I also would like this to be smaller so now I'm just gonna go ahead and delete this and then maybe I'm just going to copy my tasks from here just so you see how that looks I'm gonna paste them here and then I'm gonna have they start on May 28th and all of my tasks or events I actually named that event so yeah it looks good so that's it for this video if you liked it if you found it useful please give it a thumbs up if you like my tutorials please subscribe I know there's still a lot of things that I can cover regarding calendars I will keep on making videos I just didn't want to throw in everything at once so please let me know if this was too easy if this was too hard so I hope you like this I hope you found it useful and if you would like to purchase more advanced spreadsheets you can find a link to my website in the description thank you so much for watching