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: DateDiff("n", [StartDateTime], [EndDateTime])
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:
=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")
This formula uses:
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":
Seconds: DateDiff("s", [StartDateTime], [EndDateTime])
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:
PayAmount: Round(CCur(Nz(DateDiff("n", [StartDateTime], [EndDateTime]) * [HourlyRate] / 60, 0)), 2)
Note: Access 97 and earlier do not have the Round() function, but you can download one.
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|