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.

On Sun, 26 Sep 2010 12:24:22 -0700 (PDT), Kurt <(E-Mail Removed)> wrote:

>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

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

John W. Vinson wrote:
> On Sun, 26 Sep 2010 12:24:22 -0700 (PDT), Kurt <(E-Mail Removed)> wrote:
>
>> 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.

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

On Sep 27, 11:38*am, John Spencer <JSPEN...@Hilltop.umbc> wrote:
> 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
>
> John W. Vinson wrote:
> > On Sun, 26 Sep 2010 12:24:22 -0700 (PDT), Kurt <heislerk...@gmail.com> wrote:
>
> >> 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.