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.