Calculate time difference

[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
better way to go about this? I've tried the DateDif function but if
the difference spans midnight it always miscalculates.

Thanks.

 

[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
better way to go about this? I've tried the DateDif function but if
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/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[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
better way to go about this? I've tried the DateDif function but if
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.

 

[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
better way to go about this? I've tried the DateDif function but if
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.