Collaborate on Invoice Template for Hours Worked for Customer Support with Ease Using airSlate SignNow

Invoice template for hours worked for Customer Support

welcome to another tech help video brought to you by accesslearningzone.com i am your instructor richard rost in today's video we're going to build a timesheet and i'm going to show you how to calculate the total hours worked whether it's for employees or you're building customers or assigning them to jobs or whatever in your microsoft access databases today's question comes from michelle from dunedin new zealand i hope i'm pronouncing that correctly she says i watched your video on calculating total time spent on a job but how can i total that up in a form footer i have a time sheet showing customers a time in and a time out i'd like to calculate the total hours worked in the form footer and she's right in the original video that i did where i show you how to calculate the total time spent on a job i only pretty much built the spreadsheet in a query and then i showed you an aggregate query how to add them all up but we didn't do it in the form footer so let's put all these calculations in a form footer so you can see exactly how many hours were worked for the job now this will work whether you're doing employee timesheets or you're calculating job costing for a customer or you're doing time and billing it's all the same stuff it's a time in a time out and total hours worked so you can go watch this one if you want to this is my total time spent video i'll put a link down in the links section but i'm going to recreate this real quick because the original video was recorded four months ago before i started doing my cool tech help free template so i'm gonna put this inside of that so go download a copy of my tech help free template if you want to it's a free download from my website you'll find a link down below so let's create a table we'll call this our timesheet so we got a timesheet id that's our auto number now you can put a customer id in here i'll say we're billing customers for our time this will be a number of type long integer right that's our foreign key if you're not familiar with that go watch my video on relationships and again this could be an employee id or a job id or whatever you want to relate this to it doesn't really matter okay we've got a start time which will be a date time field it can be a date or a time or both right it's preferably you enter a full date time so january 1st at 3 pm all right the calculations work best that way and an end time right or you can call this clock in clock out whatever you want to call it doesn't matter all right let's save this time sheet t timesheet table primary key is our id of course close it and let's put some data in it now we can leave the customer id 0 for now you could put a combo box in there so you can easily see that but for now it doesn't matter all right so let's put in here 1 1 at 9 am to 1 1 at 5 pm calculations are going to go in a query right how about one two at eight a.m one two at three p.m and i'll do one more one three at 10 a.m to 1 30 at 11 pm that's a long day okay and again at this point the customer id is meaningless we'll we'll fill that in later all right close that save changes yes now let's go create a query to do our calculations just like the last video create query design all right we're going to bring in the time sheet table you can optionally bring in the customer table if you want to see the customer details here but we don't need to all right let's bring down the star so we've got all the fields in here now the first thing we're going to calculate is the minutes worked it's best if you do your calculations based on minutes all right don't try to divide up and do hours and stuff yet we'll take the the minute difference using date diff so i showed in the last video and then based on the minutes you can easily derive the hours by dividing by 60. okay so we're gonna say minutes worked is going to be date diff if you've never used date dip before go watch my date diff video i'll put a link down below stop right now pause this go watch date diff get familiar with date diff okay n for minutes is our delimiter and not our delimiter our our uh our property i couldn't find the word right there right h for hours um y for years yyyy actually four y's for years i got a whole list of all the different ones in my date that video right months is m so minutes is n use n for minutes all right then we got the time in the time out so the start time end time okay let's save this time sheet q let's take a look at what we got there's the difference in minutes between that and that all right make sure it looks about right if you want to test one come down here you can you can do data entry one four at nine am one four at ten am should give you sixty all right if i change that to ten thirty now it should be up to ninety all right so make sure that's working looks good looks good everything's good so far all right now we've got our minutes worked we can shrink that up a little bit we don't need to see the whole thing now that i know the minutes worked i can calculate the hours work now this will be the hours as a fraction you'll see a decimal point all right so hours worked is going to be minutes worked divided by 60. it's that simple okay there we go all right eight hours seven hours 13 hours 1.5 hours okay if i come in here and make this 11 15 i'll see a quarter hour on that one now okay now you might want to know how many whole hours is that because if we know the whole hours then we can derive the minutes that are left over okay so back to the query all right shrink that up we got we got the hours worked now we're going to take the whole hours whole hours worked all right we're just going to take that same calculation and run it through the int function all right the int of hours worked all right run that there's the int it just chops off the fractional component all right and if you change this to like 1145 it doesn't round up it just chops it off that's what i want the whole thing all right even if this is 11.55 all right it's most of an hour nope you still get 13 there and this as you can see sometimes you'll get very long numbers out of that one you may want to round that value this is one of the reasons why i don't like using the divide by 60 to calculate this because you get these real sometimes wacky little fractions in there i don't rely on this number this is just for display all right i use minutes worked but you can you can come in here for hours worked and round this guy to maybe two decimal places all right and that'll fix that of okay all right so now we got the whole hours worked now we can figure out how many minutes are left over by multiplying that by 60. all right makes sense you got the whole hours worked multiply that by 60 and you get the minutes that are left over okay a lot of this is just basic logic and math there's not a lot of crazy database stuff going on here right minutes left equals the total minutes worked actually we're in a query now i'm used to i'm used to writing in vb so i have to go back to my query query knowledge here all right minutes left colon minutes worked minus and then whole hours worked times 60 minutes okay basic math okay that's eight even hours so there's no minutes left all right that's 55 extra minutes onto that hour there's 13 hours 55 minutes all right see how that works all right one more column let's display that as a duration so you don't see 0.25 hours you see 15 minutes all right that's a lot of times when you're doing billing that's how you want to display it so now we're basically going to convert this into a text string so duration okay is going to be whole hours worked and a colon and minutes left all right that's just basic concatenation if you've never done string concatenation before go watch my concatenation video all right see a lot of this stuff builds on other stuff that's why a lot of times people they have a certain thing they want to do that requires advanced stuff like vba and they try jumping right into the developer class right or trying to write some vba they found online i get this a lot people say oh i found this code online how do i do it no well you don't have all the understanding that goes underneath it okay i was just telling a customer this story yesterday in fact that my daughter when she was like eight or nine years old we went to see a movie and she was sitting there before the movie starts and she's got her popcorn and she's like daddy where's the moon come from right and i'm trying to explain this to an eight-year-old i'm like well four and a half billion years ago there was a mars-sized planetoid they named thea which struck the earth and blew most of our mantle out into space and it orbited the planet for a while and then it coalesced into the moon and she just looked at me and okay and went back to eating a popcorn all right it's the same thing with if you have basic knowledge of access and you're trying to do developer level stuff you've got a whole lot of other stuff you've got to understand first before you can just jump into it okay i wish i had time to hold everyone's hands i really do but there's only so many hours in the day so i'm just gonna point you to videos or like this i can try to explain in the tech help video but i can only do so many of these today too so i'm trying people i'm trying all right so now we got that all right now usually you want to see a two digit minute right that's the convention it'd be eight hours and like zero zero minutes so we'll just format that to show two decimal points so wrap this in the format function right format that comma and then inside of quotes 0 0 just like that and now you'll see that duration looking like that okay so make sure this works hour and a half right 1.5 hours 90 minutes this this is good this this this is bulletproof i can't see any way that this would be wrong if you find a bug with this let me know i'll give you a free year's subscription to my website okay now let's put all of this into a form or use a continuous form save changes to that yes we already have a continuous form from our template again you can go watch how that was constructed by going to watch this video alright we'll call this our timesheet f all right find the time sheet f down here right click design view bind the timesheet to that new query all right use the query so you have your calculations in the query you have to do many calculations in the form the form records will have no calculations in them at all they're all done in the query underneath we'll have some calculations in the form footer in a minute when we add all them up okay all right now i'm going to delete this guy and that label and um yeah we'll just delete that too we'll just bring everything in new add existing fields all right don't really need the timesheet id but i'm going to bring it in anyways uh the customer id yeah sure we'll turn that into a combo box we'll probably do that in the extended cut we don't need that for now for you to get the basic understanding of how this works all right start time end time now bring in minutes work although we don't need to see it it's nice to have there we don't need hours work we don't need whole hours work we don't need minutes left because those are just needed for the calculation okay so let's bring in duration just for the just for the appearance okay click and drag all these drop in the detail section all right i'm going to get rid of those labels delete the id which we really don't need will just slide over there give me a second i know there's the layout trick that we learned to do this but i find it easier just to do it like this to be honest it only takes a second folks okay end time goes there minutes worked which that can be smaller and duration can be smaller too unless you're working with gigantic times i'm going to select all that right click size to grid make it all nice and even now this that and that can all be gray i like to gray these things out because it just tells the user you can't change those all right this we're going to change into a combo box in the extended cut and let's drop some labels on top we got customer id start time end time minutes and duration all right so just slide these up here like that all right customer start and minutes and we'll just call this time all right line these guys up start end time minutes i'm almost as fast as doing with the layout trick i've gotten good at it over the years all right let's save this close it open it back up again take a peek see what we got all right stuff stuff needs to be formatted down a little bit let's let's let's adjust this stuff here design view take all this stuff let's left align everybody and let's format these guys right click properties let's bring up the format in here we're going to go m slash d slash yy space h colon nn and let's do ampm okay right one digit month two if needed same with the year same with the hour two digit minute two digit year and am and pm that should fit in that space perfect okay again these calculations should all be correct all right now we're going to add up stuff in the form foot we're going to app this total number of minutes this ignore it that's just for the looks this is the number that we want right here okay it's the only number i care about don't use those fractional hours because they're not exact i don't care about the whole hours and the minutes left that was just to make this number for appearances this is the only number that you're going to use in your math unless you have to do calculations to the second i'm assuming that everyone is billing by the minute all right if you if you're doing more precise calculations where you need seconds then you need to do your calculations based on seconds okay but i think for 99.9 percent of the business world we use minutes right you build the nearest minute if not in 15 minute increments and i've got a video on that too how to how to make your billing to the 15 minute increments i'll put a link to that down below too 15 minute increments okay that's a free video as well alrighty so we want to take the sum of this guy now this is something called form footer totals and i have a video on form for the totals you should go watch that too i'm going to put a link to that down below forum footer totals i have so many videos that you need to know that are all free go watch them before you try to comprehend this stuff all right so i'm just going to take this field copy and paste it down here in the form footer okay open it up okay now it's minutes worked but i want the sum of all of the minutes worked on the form so change the control source to equals sum of all of that now i don't want to leave it text 15 so i call it sum minutes worked save it close the form i always close the form and reopen it when i make design changes and that should be the total of all that there's the total minutes worked up here this is what you'll use for the rest of your calculations all right now you don't have to display them but you need them so let's do the same stuff we did in the query just down here in the form all right copy paste slide you over here now we don't need this guy but i still want it all right just to show you how this stuff works all right we'll do the hours worked this will be that fractional one again all right equals the sum minutes worked there it is it even pops up in the little helper thing for you you can click on that if you want to all right some minutes worked divided by 60. and again if you want to round that guy to 2 so it doesn't look all crazy with a fraction round that comma 2. if you're not familiar with the round function go watch my video on the round function rounds the number to the nearest decimal places okay open it up there you go that's that number that minutes is 30.42 hours well how many whole hours is that you asked well let's figure that out too let's copy this one copy paste slide it up next to it all right this will be whole hours worked and what's that going to be equals the sum excuse me the int of the sum minutes worked divided by 60. chop off the fractional component save it close it open it up that's 30 whole hours beautiful now how many minutes are left over we should know how to do this right take this guy right click on it copy paste slide it over here again we don't need it but we need it for the calculations minutes left we could do all this in one big monster calculation by the way i don't like doing that i like breaking it up into smaller steps it's much easier to read later on too alright this is the sum minutes worked minus the whole hours worked times 60. that's how many minutes are left over open it up all right 30 hours 25 minutes left over right because 30 hours would be 1800 so there's 25 minutes left makes sense okay now i can display that cool duration again right we'll just copy any one of these copy paste slide that up over here and exit duration or under duration i should say now you can't sum that up that's technically a text field it's just displayed a certain way okay but we're going to call it sum duration and it's going to be equals whole hours worked and a colon and we could do that that format thing again format minutes left comma zero zero to make sure it's two digits save it close it open it up again there's my hours and minutes 30 hours 25 minutes right again that makes sense right 1800 would be 30 hours 25 minutes left over all right wanna see it in english down here below that we can do that too copy this copy paste that's just adjusting what's in here right all right let's call this uh duration english and we'll just change what's in here let me zoom in so you can see this better whoops i lost there we go come on down all right hours worked equals that many space hours comma and that many and space minutes and if you do it this way you don't really need the format because you could put one minute or minutes yeah you could do something where you you only show a minute or minutes with maybe an if function maybe i'll show you that if i'm feeling lucky let's see here open that up there you go 30 hours 25 minutes now this you could put into your billing if you want to all right actually this is probably better that's just neat to show all right i'll show you the little if function trick since you bugged me all right watch this there's another video called if the immediate if i i f okay go watch that all right so you understand this all right minutes left all right so this so this right here we're gonna we're gonna chop that off and at the end here i'm gonna say and if minutes left equals one comma put nothing there otherwise put an s there so if it's one it just says minute if it's zero or two or more it'll put an s on the end of minutes you could do the same thing with hours right there chop that off and say if whole hours worked is equal to one put nothing there otherwise put an s there okay and then we have to close up the if and then and and then put our comma space there see that how that works with the little if i don't want to confuse anybody if you're confused by this back up 30 seconds and stop watching because i'm done no go watch the if function it's basically an if then statement if this then do that else do that all right now save it open it back up again this is the kind of cool stuff i usually reserve for the extended cut people so for those of you who are thinking about becoming members you want to see some neat stuff all right so we've got to make this come out to one minute so let's make this oh i can't change that can i let's change this to zero zero let's see what we got alright 30 minutes all right let's change this to 01. 29 hours one minute see ricky does know what he's talking about let's change this to zero there you go you could even have this whole thing here disappear if it's zero but i'm not going to get into that far you can do nested if functions and all kinds of crazy stuff which i do cover in my full classes by the way okay so does that answer your question now you got the times up here you got the times all correct in the form footer that's how you do it all right want to learn more about making timesheets well in the extended cut 37 minutes long we'll add that combo box to the timesheet so you can pick who the customer is that you're billing and some notes down on the bottom right this is a phone console this is a warp drive overhaul whatever you want to bill for right then we'll track whether or not it's been invoiced already okay so then i can open up one customer's billings hit create invoice and then boom it just puts the invoice together right puts in the hourly billing figures out the total number of hours puts in our unit price for our hourly rate click another button there's your printable invoice all right that's all covered in the extended cut for members silver members and up get access to all of my extended cut videos i will also include links down below to other videos on similar topics like here's the original date diff that covers how to calculate total time spent this one just doesn't go into the form footer totals i've got my advanced date time seminar that covers all kinds of stuff having to deal with dates and times counting work days between two dates right a holiday exclusion table so if you want to say this person started work on a friday and a tuesday but that monday plus the whole weekend we don't count all kinds of pop-up notifications recurring appointments all kinds of stuff in here okay how do you become a member click the join button below the video after you click the join button you'll see a list of all the different types of membership levels that are available silver members and up will get access to all of the extended cut tech help videos live video and chat sessions and more gold members get access to a download folder containing all the sample databases that i build in my tech help videos plus my code vault where i keep tons of different functions that i use platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses these are the full-length courses found on my website and not just for access i also teach word excel visual basic asp and lots more but don't worry these free tech help videos are going to keep coming as long as you keep watching them i'll keep making more if you like this video please give me a thumbs up and feel free to post any comments that you have i do read them all make sure you subscribe to my channel which is completely free and click the bell icon and select all to receive notifications when new videos are posted click on the show more link below the video to find additional resources and links you'll see a list of other videos additional information related to the current topic free lessons and lots more youtube no longer sends out email notifications when new videos are posted so if you'd like to get an email every time i post a new video click on the link to join my mailing list now if you have not yet tried my free access level 1 course check it out now it covers all the basics of building databases with access it's over three hours long you can find it on my website or on my youtube channel and if you like level one level two is just one dollar and it's also free for all members of my youtube channel at any level want to have your question answered in a video just like this one visit my tech help page and you can send me your question there click here to watch my free access beginner level 1 course more of my tech help videos or to subscribe to my channel thanks for watching this video from accesslearningzone.com