Calculate time difference

K

Kurt

In a query, I need to calculate the elapsed time (in hours and
minutes) between two times (which may span midnight), and round to the
nearest quarter. I will later multiply the elapsed time by an hourly
rate to calculate an amount due.

StartTime and EndTime are both Date/Time fields in Medium Time format.

To calculate the elapsed time, I'm using this:

TotalHrs: Format([StartTime]-1-[EndTime],"Short Time")

But I take it this produces a string (e.g., "01:20"), which means I
can't round it, nor use it in a subsequent calculation. Is there a
the difference spans midnight it always miscalculates.

Thanks.

J

John W. Vinson

In a query, I need to calculate the elapsed time (in hours and
minutes) between two times (which may span midnight), and round to the
nearest quarter. I will later multiply the elapsed time by an hourly
rate to calculate an amount due.

StartTime and EndTime are both Date/Time fields in Medium Time format.

To calculate the elapsed time, I'm using this:

TotalHrs: Format([StartTime]-1-[EndTime],"Short Time")

But I take it this produces a string (e.g., "01:20"), which means I
can't round it, nor use it in a subsequent calculation. Is there a
the difference spans midnight it always miscalculates.

Thanks.

Use DateDiff instead, to calculate the integer number of minutes:

TotalMin: DateDiff("n", [StartTime], [EndTime])

You can display this as hours and minutes with an expression like

[TotalMin] \ 60 & ":" & Format([TotalMin] MOD 60, "00")

in the control source of a form or report textbox (this won't be editable
though). To calculate total hours you'ld of course divide by 60.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/

J

John Spencer

The following expression can be used to calculate elapsed time when there is
no date component and times may be over two days. For example, 11:49 PM to
1:00 AM

Number of Minutes:
(DateDiff("n",Starttime,Endtime) + 1440) Mod 1440

Number of Seconds:
(DateDiff("s",Starttime,Endtime) + 86400) Mod 86400

You can round (up or down) the number of minutes to the nearest 15 with an
expression like

Round(N/15,0) * 15

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
In a query, I need to calculate the elapsed time (in hours and
minutes) between two times (which may span midnight), and round to the
nearest quarter. I will later multiply the elapsed time by an hourly
rate to calculate an amount due.

StartTime and EndTime are both Date/Time fields in Medium Time format.

To calculate the elapsed time, I'm using this:

TotalHrs: Format([StartTime]-1-[EndTime],"Short Time")

But I take it this produces a string (e.g., "01:20"), which means I
can't round it, nor use it in a subsequent calculation. Is there a
the difference spans midnight it always miscalculates.

Thanks.

Use DateDiff instead, to calculate the integer number of minutes:

TotalMin: DateDiff("n", [StartTime], [EndTime])

You can display this as hours and minutes with an expression like

[TotalMin] \ 60 & ":" & Format([TotalMin] MOD 60, "00")

in the control source of a form or report textbox (this won't be editable
though). To calculate total hours you'ld of course divide by 60.

K

Kurt

Thank you for the suggestions. It was surprising that date/time
calculations are so difficult and unintuitive.

This is what I ended up doing:

1. Calculate the total number of minutes, which may be over two days:

TotalMin: (DateDiff("n",[Starttime],[Endtime])+1440) Mod 1440

e.g., If StartTime = 9:30 PM and EndTime = 11:50 PM, TotalMin = 140

2. Round the total minutes to the nearest 15 (very unintuitive, since
I want to round the hours to nearest quarter [i.e., 25]. But rounding
the minutes to 15 will get you there):

TotalMinR: Round([TotalMin]/15,0)*15

e.g., 140 -> 135

3. Calculate the number of hours, rounded to the nearest quarter:

TotalHrsR: [TotalMinR]/60

e.g., 135/60 = 2.25

4. Calculate the amount to charge (multiply the total hours by an
hourly rate of \$55)

Charge: [TotalHrsR]*55

e.g., 2.25*55 = \$146.25

The following expression can be used to calculate elapsed time when thereis
no date component and times may be over two days.  For example, 11:49 PM to
1:00 AM

Number of Minutes:
(DateDiff("n",Starttime,Endtime) + 1440) Mod 1440

Number of Seconds:
(DateDiff("s",Starttime,Endtime) + 86400) Mod 86400

You can round (up or down) the number of minutes to the nearest 15 with an
expression like

Round(N/15,0) * 15

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
In a query, I need to calculate the elapsed time (in hours and
minutes) between two times (which may span midnight), and round to the
nearest quarter. I will later multiply the elapsed time by an hourly
rate to calculate an amount due.
StartTime and EndTime are both Date/Time fields in Medium Time format.
To calculate the elapsed time, I'm using this:
TotalHrs: Format([StartTime]-1-[EndTime],"Short Time")
But I take it this produces a string (e.g., "01:20"), which means I
can't round it, nor use it in a subsequent calculation. Is there a
the difference spans midnight it always miscalculates.
Thanks.
Use DateDiff instead, to calculate the integer number of minutes:
TotalMin: DateDiff("n", [StartTime], [EndTime])
You can display this as hours and minutes with an expression like
[TotalMin] \ 60 & ":" & Format([TotalMin] MOD 60, "00")
in the control source of a form or report textbox (this won't be editable
though). To calculate total hours you'ld of course divide by 60.