There are 2 basic methods to calculate the difference between 2 times. For instance, what is the duration between 9:00am and 5:45pm, the answer is 8.75 hours OR 8 hours and 45 minutes. You may want to calculate times for payroll and timecard purposes, or maybe you’re just interested in how long a task takes to complete. The clock seconds command returns the time in seconds since the epoch. The date of the epoch varies for different operating systems, thus this value is useful for comparison purposes, or as an input to the clock format command.
Microsoft Access Tips for Casual Users
Provided by Allen Browne, November 2003. Updated March 2007.
How do you calculate the difference between two date/time fields, such as the hours worked between clock-on and clock-off?
Use DateDiff() to calculate the elapsed time. It returns whole numbers only, so if you want hours and fractions of an hour, you must work in minutes. If you want minutes and seconds, you must get the difference in seconds.
Let's assume a date/time field named StartDateTime to record when the employee clocks on, and another named EndDateTime for when the employee clocks off. To calculate the time worked, create a query into this table, and type this into the Field row of the query design grid:
Minutes is the alias for the calculated field; you could use any name you like. You must use 'n' for DateDiff() to return minutes: 'm' returns months.
To display this value as hours and minutes on your report, use a text box with this Control Source:
This formula uses:
- the integer division operator () rather than regular division (/), for whole hours only;
- the Mod operator to get the left over minutes after dividing by 60;
- the Format() function to display the minutes as two digits with a literal colon.
Do not use the formula directly in the query if you wish to sum the time; the value it generates is just a piece of text.
If you need to calculate a difference in seconds, use 's':
You can work in seconds for durations up to 67 years.
If you need to calculate the amount of pay due to the employee based on an HourlyRate field, use something like this:
The formula:
![Tcl Program To Calculate The Time Difference Between Two Times Tcl Program To Calculate The Time Difference Between Two Times](/uploads/1/2/5/8/125865370/805418519.jpg)
- calculates the minutes;
- multiples by the hourly rate;
- divides by 60 (minutes in an hour);
- converts a Null to zero (because currency cannot be Null);
- typecasts to Currency (the desired output, and for accurate rounding);
- and rounds the result to two decimal places (i.e. the nearest cent).
![Times Times](http://www.wikihow.com/images/6/63/CalculateDiffinNumbersStep4..png)
Note: Access 97 and earlier do not have the Round() function, but you can download one.
Alternative functions
Doug Steele and Graham Seach have developed a more complete DateDiff. It shows the difference between two date/time values in days and hours and minutes and seconds.
To compare a date/time value to now, and return something readable such as 'next week', or 'in 5 hours', or '2 years ago', see Constructing Modern Time Elapsed Strings.
Home | Index of tips | Top |