Total Time Spent
By Richard Rost
6 years ago
Calculate Total Time Spent on a Job with DateDiff
In this Microsoft Access tutorial, I will teach you how to calculate the difference between two times. You can use this for employee time clocks, job costing, or just about any database where you need to figure out the difference between a start time and an end time in minutes and/or hours.
Adam from Fort Dodge, IA (a Developer Student) asks: I have a series of jobs that have a start date/time and an end date/time. I need to be able to calculate the total time worked on each job so I can bill my customers accordingly. Additionally, sometimes the job might start on a Thursday and end on a Monday. I need to exclude weekends, after hours (5pm to 8am the next day) and lunch breaks (noon to 1pm). How do I do this?
Members
I'll show you how to add ranges of time to exclude from your calculations (such as when the business is closed, lunch hour, breaks, etc.) and exclude weekends.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
 Links
Difference in Dates: https://599cd.com/DifferenceInDates
Aggregate Queries: https://599cd.com/Aggregate
DateDiff Function: https://599cd.com/DateDiff
IIF Function: https://599cd.com/IIF
NetWorkDays: https://599cd.com/NetWorkDaysAccess
Intro In this video, I will show you how to calculate the total time spent on a job in Microsoft Access. We will create a table to track job start and end times, use queries to calculate the difference between clock-in and clock-out data, and learn how to display total minutes, fractional hours, and formatted time values. I will also show you how to group and sum total hours per job for billing or payroll purposes. This tutorial covers the essential steps to track and calculate work time using Access queries.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. My name is Richard Rost, your instructor.
In today's video, I am going to show you how to calculate the total time spent on a job. You can use this technique whether it is employees at a time clock or whether you have projects and are doing job costing and you are billing customers for the total hours spent on a job.
In any case, we are going to take a time in and a time out. We are going to calculate the total minutes or hours and then I will show you how to calculate the total per job.
Today's question comes from Adam from Fort Dodge, Iowa, one of my developer students. Adam says, I have a series of jobs that have a start date and an end date. I need to be able to calculate the total time worked on each job so I can bill my customers accordingly. Additionally, sometimes the job might start on a Thursday and end on a Monday. I need to exclude weekends, after hours from 5pm to 8am the next day, and lunch breaks from noon to 1pm. How do I do this?
Well Adam, I am going to tackle the first part in this video and we will save the second part for the extended cut for the members video.
Figuring out the total time between a start time and an end time is relatively easy and I am going to show you how in just a minute. Creating exclusion zones in the middle there and excluding things like weekends is pretty tough and that is going to involve some programming.
But let's tackle the first part right now.
Here I am in a blank database. Let's create a table to store our information. This will be my time clock table so we'll start off with an ID. That is my auto number. Every table should have one. Then I am going to put in here a job ID. Now if you are using this to track employees, that could be the employee ID or customers, customer ID, whatever you want to put in there. I am going to use job ID.
Then we are going to have a time in, a date time and a time out. You could put date time if you want to for the field name. I am just going to use time in and time out. You should track this with a full date time value instead of just a time if you ever plan on going across midnight. So if you are going from Monday to Tuesday, for example, an overnight shift or like Adam's database where he's got jobs that go over multiple days, then you want to use full date time values in there. But you can just call it time in, time out. That's fine.
Let's save this. I'll call this my time clock T. Primary key, yes. That's the ID right there. Let's put some sample data in here.
So, job one. Let's say this was started on 1/11 at 9 a.m. and went from 1/11 to 3 p.m. Same job. Next day goes from 1/12 at 10 a.m. to 1/12 at 5 p.m.
Next job comes in, job two. This one, they work on it on 1/12 from 9 a.m. until 1/14 a couple days later. I forgot to put a time and that is OK. We'll just come back over here and change it to 3 p.m. So that went across a couple days there.
Now this is easier if you track these times as the employee is working or they clock in, they clock out. In other words, they come in in the morning, they clock in, they take a lunch break, they clock out at noon, then clock back in and that is another record for 1 o'clock. That is the best and easiest way to set your database up. But that involves the most work on the part of your employees. They have to remember to clock in and clock out.
Let's put a couple more records in here. Let's say job one gets some more time on it. 1/6 at 3 p.m. to 1/6 at 5:30 p.m., a couple hours there. Then back to job two one more time. 1/10 at 9 a.m. to 1/11 at 3 p.m.
Got some sample data. That should be enough for now. So now we are ready to make our query.
Now the query is going to do the bulk of the calculations for us. So close the table down and save changes. Sure. Create query design. I am going to bring in my time clock table. Close that up.
What do we want to see? I do not care about the ID. Let's bring in the job ID. Let's bring in time in and time out. Add ID if you want it. I do not really care for it right now.
I am going to save this as my time clock Q.
Right now, we just have the same information we typed in.
Now, there are lots of different ways to take the difference between two date time values using Access. I did a video a few years ago on how to simply use math. You can subtract one time from another or one date from another, and you get a difference in days because in Access one day is basically equal to one. So if I subtract time two from time one and I get one, that is one whole day. Well, if you divide that by 24, then you have hours, divide that by 60, then you have minutes. It is not perfect, but it is good enough. It is simple enough.
Today I am going to teach you how to use DateDiff. I have also done a free video on DateDiff. I am going to put links to both of these videos down below if you want to go watch those first. I only showed you how to do it with days and dates, so I am going to show you how to use it with times in this video.
Here is DateDiff and the parameter that you use to tell DateDiff what you are subtracting. Here is quarter, month, day of the year, day. Then we have weekday, week, hours, minutes and seconds. Notice that minute is n because month is m. Remember that. It is very important. I mess it up all the time. Minute is n.
I am going to come in here and calculate the total minutes between time out and time in.
Total minutes is going to be DateDiff("n", [Time In], [Time Out]). Let's save that and see what we got.
Look at that. That gives you the total minutes between these two times.
Now for most real world practical applications, minutes are good enough. You can do this with seconds if you want to. If you are doing some kind of calculations on machine timing or something really specific, you could do this with seconds. But for most employee time clocks and for job costing, minutes are good enough. If you need to bill to the second, then you will have to tweak this a little bit. I am not going into seconds right now.
Back to design view.
Once I have got the total minutes, I am just going to shrink these up a little bit here so you can see them. Now we can calculate the hours. Hours we might want as a fraction. For example, two hours, 30 minutes is two and a half hours. I am going to call this Fract Hours. Fract F-R-A-C-T hours. This is going to be the total minutes divided by 60.
Save it and run that. There you go. Fract Hours. That is six hours, that is seven hours, that is two and a half hours.
Sometimes if you have like 301 in here, let me see if I can get it to do it. Sometimes you get really long values like that, so we are just going to round that.
Back to design. Round that however you want. I am going to say round that to one decimal place. That is good enough for me for billing, tenths of an hour.
That way, if you come in here and you make this 3:15, let's say. Let's see what we got. Yes, 6.2 hours. You can go to two decimal places if you really want to. If you want to get exact, if your employees are complaining that you did not pay them for every minute. That is usually good enough.
Right here, we have enough to go by. We can use this to do the rest of our calculations. We can put this into our total hours worked or total hours we are billing for a job, and just do a summary on this. I think we are good to go.
I want to show you a couple more little tricks you can do here if you want. If you want to display it as hours and minutes or in a different format altogether.
If you look down the slide that I had, right down here, I did four hours 30 minutes like that. Or you could do 4:30 if you want to display it that way.
What we are going to do is we are going to figure out the total number of hours first, basically chop off the fractional part, then see how many minutes are left, and then display it like this. So you can display it like that, two hours 45 minutes on their job costing sheet instead of showing them, for example, 4.5 hours.
It is totally up to you if you want to do this. How do we do it? Let's go into design view again.
We are going to use fractional hours for our major calculations. But let us figure out the total hours. Now, total hours is going to be total minutes, and then we are going to use integer division by 60. Notice it is a backslash.
That backslash there is different from the forward slash, which is regular division where you get a floating point. That is integer division. The backslash says just give me the integer portion, get rid of anything after the decimal point.
Now, let us change this from total hours to whole hours. I will make that whole hours. I do not like total hours. Let us do whole hours and round it.
There is whole hours: 6, 7, 54, 2, and 30.
Now, we need to know how many minutes are left over. Minutes left is going to be total minutes minus whole hours times 60, like that. There we go. Six hours, 15 minutes left. Seven hours, nothing left. Two hours, 30 minutes left.
Now we can display this however we want to. You can say, let's call it display time 1 which is going to be whole hours and the word hours, comma, and minutes left and the word minutes, like that. Spell the whole thing out. If you are doing things like movie times, for example, you might want it like that. Six hours, 15 minutes, zero minutes.
You could even throw an IF in there if you want. For example, we could get the comma space out of there and put it in only if minutes left is greater than zero. That should get rid of the minutes if it is zero. It just puts a blank. That is the IF function, immediate IF. Whole hours and hours, if minutes left is greater than zero, then comma, put that comma space, ampersand, which is string concatenation, then tack on the minutes on the end of it.
Or, you could do any format you want. For example, display time 2 is going to be format(whole hours, "00") & ":" & format(minutes left, "00"), like that. The format "00" forces it to display with two digits for the time. Now we have that. Otherwise you would just get 2:30 instead of 02:30. If you have one minute over here, you might even get 2:1, which you do not want, so you want to force that to format with the format function.
Now I have showed you how to pull all these things apart. We could do it as fractional hours, which is how I prefer it for doing calculations. This is good for display though. If you want to show it like on an invoice or on a time sheet at the very bottom, for example, this week you were 40 hours and 30 minutes or whatever.
Now we can take this and feed it into an aggregate query. If you do not know what an aggregate query is, I have videos on it. Go watch that first. I will put a link down below. This is aggregate queries. Watch that one and come back here.
An aggregate query basically lets you take the results from a table or query and add stuff together and group it. If you look at this data here, we want to group together everything for customer one, these three records, and customer two or job two in my case, and then sum up fractional hours. That is the total amount of time to either pay this employee or bill this customer.
So we will do that in an aggregate query. Create query design. This time, I am going to bring in my time clock Q. I want the job ID because that is what I am grouping by, then I want fractional hours, that is what I am calculating on.
Now, turn on totals to make an aggregate query. I want to group by job ID and sum. There are a whole bunch of functions in there. Sum fractional hours.
Save this as job cost Q, and then run it. There is my total job cost. It is the sum of fract hours. We can change that by giving it an alias right here: total hours. Run, and the same. Group sums by job, there is a unique record for each job ID, and total hours, the hours are summed up.
That covers part one of Adam's question: how to calculate the time worked on each job between the start date and the end date.
Part two of the question we will cover in the members extended cut. You have to exclude weekends, after hours, and breaks. How do you do that? Well, I built it in a 40-minute extended cut where we basically set up an exclusion table. You can see it right down here: exclusion start and end times. You can make as many exclusion zones as you want. Here we have 5pm to midnight is excluded, 12am to 9am is excluded, 12am to 8:59am. We go one minute shy because at 9am you actually start work again. Then noon to 1 is our lunch break. We have a coffee break here from 3 to 3:30 to test that. You can set up as many as you want in the exclusion table. We exclude weekends as well. This way, if you have a project where you have just got a start time and end time and you want to just exclude all of these in the middle zones and figure the total amount of billable time, to either bill a client or pay the employee or whatever you are doing. Hit the button, it takes a second, and it calculates that.
That is the 41-minute extended cut for Silver members and up.
How do you become a member? Click on the Join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the Join button, you will see a list of all the different perks that are available: Silver, Gold, Platinum, and Diamond.
These TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making them and they will always be free.
If you enjoyed this video, please give me a like and share. Click on the Subscribe button to subscribe to my channel and be notified of any new releases.
Check for additional resources down below the video. Click the Show More button and you will see a list of other links to other videos, downloads, resources, lessons, and lots more.
If you have not yet tried my free Access Level 1 course, it is three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just $1 and that is free for my members.
Want to have your question answered in a video just like this one? Visit my TechHelp page and you can post your question there.
Be sure to stop by my Access forum on my website. Also, look for me on Facebook, Twitter, and of course YouTube.
Once again, my name is Richard Rost with AccessLearningZone.com. Thanks for learning with me and I will see you next time.Quiz Q1. What is the primary objective of the technique demonstrated in this video?
A. Calculating tax rates based on employee income
B. Calculating the total time spent on a job
C. Summing up project costs for multiple clients
D. Tracking inventory levels for different warehouses
Q2. Why is it important to use full date time values instead of just time values when tracking clock-in and clock-out times?
A. Full date time is required for sorting records alphabetically
B. Full date time helps track which employee performed which task
C. Full date time allows accurate tracking over midnight or multiple days
D. Full date time ensures data can be exported to Excel
Q3. In the example, which field tracks the relationship between time records and specific jobs?
A. Employee ID
B. Customer Name
C. Job ID
D. Record Number
Q4. Why is using individual clock-ins and clock-outs for each period (like before and after lunch) considered the best practice?
A. It results in less data entry work for employees
B. It simplifies calculations and precisely tracks actual work time
C. It allows employees to estimate break times
D. It is the only way Access will store the data
Q5. What Access function is primarily used in the video to calculate the number of minutes between two date time values?
A. DatePart
B. Now
C. DateDiff
D. Sum
Q6. When using DateDiff in Access, what parameter letter should be used to specify "minutes"?
A. m
B. d
C. t
D. n
Q7. How do you calculate fractional hours from total minutes in Access?
A. Divide total minutes by 100
B. Divide total minutes by 24
C. Divide total minutes by 60
D. Multiply total minutes by 0.75
Q8. Why might you want to round fractional hours to one decimal place when billing?
A. To minimize rounding errors in calculations
B. To ensure employees are underpaid
C. To simplify display and billing calculations
D. To avoid overflow errors in Access
Q9. What is integer division, and which symbol represents it in Access?
A. Division with decimals, represented by /
B. Division with decimals, represented by \C. Division with only integer results, represented by /
D. Division with only integer results, represented by Q10. How do you determine the number of leftover minutes after calculating whole hours from total minutes?
A. Add total minutes to whole hours times 60
B. Subtract whole hours times 60 from total minutes
C. Divide total minutes by whole hours
D. Multiply whole hours by 24
Q11. Which string operation is used to combine fields and text in display formats in Access?
A. Plus sign (+)
B. Asterisk (*)
C. Ampersand (&)
D. Percent (%)
Q12. What is the purpose of using the Format function with "00" when displaying hours and minutes?
A. Ensure all numbers are displayed in scientific notation
B. Show minutes as text instead of numbers
C. Force each part to display with two digits for consistency
D. Remove all leading zeros from display
Q13. What is an aggregate query in Access used for in this context?
A. To delete records in bulk
B. To group records and sum the hours per job
C. To split time entries by the day
D. To list all jobs alphabetically
Q14. What is one advanced feature mentioned for Silver members regarding time calculation?
A. Excluding negative values from results
B. Including overtime calculations automatically
C. Excluding time from weekends, after hours, and breaks
D. Calculating payroll taxes
Q15. For displaying calculated time like "4:30", why is it better to use Format to enforce two digits for hours and minutes?
A. To make sure Access imports it as text
B. To prevent hours like "2:1" from displaying instead of "02:01"
C. To allow larger values in the field
D. To keep times sorted by date
Answers: 1-B; 2-C; 3-C; 4-B; 5-C; 6-D; 7-C; 8-C; 9-D; 10-B; 11-C; 12-C; 13-B; 14-C; 15-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on calculating the total time spent on a job, a topic that is useful for tracking employee hours, managing project billing, or determining labor costs for customers. This method involves using a time in and a time out for each work period, then calculating total minutes or hours worked before aggregating these results by job.
Adam, one of my developer students, submitted a question about this. He needs to calculate total work time for each job in his database so he can bill customers accurately. His jobs can begin on one day and finish on another, sometimes spanning multiple days. He also wants to exclude weekends, after hours (from 5pm to 8am the next day), and lunch breaks between noon and 1pm.
In this video, I address the first part of the problem: calculating the straightforward difference between start and end times. Handling the exclusions for weekends, after hours, and lunch breaks is a more advanced task that I cover in the Extended Cut video for members.
To begin, I create a table in a new Access database to store job time records. Each record includes an ID (as an AutoNumber primary key), a Job ID, and fields for the time in and time out. I recommend storing these as full date/time values, especially if your jobs might cross over midnight or multiple days.
After entering some sample data for different jobs, I set up a query to perform the calculations. In Access, you can subtract two date/time values directly, which yields the difference in days. Converting this to hours or minutes is a matter of multiplying or dividing by 24 or 60, but a more robust approach is to use the DateDiff function. DateDiff allows you to specify the units you want to measure (such as minutes, hours, or days). Remember that for minutes, you use 'n' as the parameter because 'm' is reserved for months.
Using DateDiff, I can find the total minutes between each time in and time out. For most practical purposes like payroll or job costing, tracking by the minute is precise enough, though you could use seconds if you need more detail.
Once I have the total minutes, I calculate fractional hours by dividing total minutes by 60. For better readability and billing, I round these hours to a single decimal place, making them easy to use in invoices or reports.
For a more user-friendly display, you might want to express time as hours and minutes instead of a decimal. You can extract the whole number of hours using integer division and find the remaining minutes. This allows you to display results like "6 hours, 15 minutes" or in a digital clock format such as "06:15." The Format function in Access helps ensure that numbers always appear in two-digit format, which looks cleaner and is especially useful for time displays.
After setting up these calculations, the next step is to total the hours by job. For this, I recommend using an aggregate query. This type of query groups records by job ID and sums up the fractional hours for each job, giving you the total hours worked per job. This is extremely helpful for both payroll and billing scenarios.
That completes the first part of Adam's question: calculating the total worked time on each job, based purely on the recorded start and end times.
The second part of the problem, which involves excluding nights, weekends, and breaks, is a more advanced challenge. In the Extended Cut video, I walk you through creating an exclusion table to list all the periods you want to exclude, such as after-hours, weekends, lunch breaks, and even coffee breaks if needed. Using this approach, you can define as many exclusion zones as necessary. With some additional programming, Access can then calculate only the billable or payable hours, ignoring the specified exclusion periods.
If you are interested in learning how to set up these advanced exclusions, the Extended Cut for members demonstrates all the necessary steps.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Creating a time clock table to track job times
Entering sample job and time clock data
Using full date/time values for clock-in and clock-out
Understanding time data spanning multiple days
Creating a query to calculate time differences
Using DateDiff to calculate minutes between two times
Converting total minutes to fractional hours
Rounding fractional hours for billing purposes
Displaying hours and minutes in separate fields
Using integer division to separate whole hours from minutes
Formatting time as "X hours, Y minutes" or "HH:MM"
String concatenation techniques for custom displays
Creating an aggregate query to sum hours by job
Grouping job records and calculating total hours per job |
