Queries - Rounding time, hours up and down, based on minutes (1 Viewer)

[ifjzettner]

Hello all,

Thanks for opening my thread.

I'm calculating total times in a query, based on the start time, the end time and the total time, which is simply end time minus start time.

What I wish to achieve: if the minutes show 0-9, round down to the nearest hour. Else, round up to the nearest hour. So 13:01 returns 13, 13:10 returns 14.

So far, I have:

[start_time]
[end_time]
[total_time]

I need:

[rounded_time]

I get total time by this method:

total time: CDate(IIf([start_time]<[end_time];[end_time]-[start_time];(#23:00:00#+#1:00:00#)-([end_time]-[start_time])))

I have a basic understanding of rounding, this below works:

rounded_time: Round(([end_time]-[start_time])*24)

This above returns the hour only, and rounds 13:29 to 13 and 13:31 to 14.

I tried experimenting with IIf, CDate and minutes, but failed so far.

Thank you for your suggestions.

Best wishes,
Tamas

 

[Rx_]

TimeSerial(Hour([mytime]),(Minute([mytime])\30)*30,0)
This rounds time to a 30 minute period.
Maybe that could be rounded up or down depending on the requirement.

The TimeSerial() expression can be used directly in a query.

 

[billmeye]

You could use the Mod function. The Mod function returns the remainder based on whatever you are using for the divisor.

TotalMinutesExtra = (Datediff("n",#05/14/2013 9:00:00AM#,#05/14/2013 10:15:00AM#) mod 60)

If TotalMinutesExtra < 10 then
TotalTime = datediff("h",#05/14/2013 9:00:00AM#,#05/14/2013 10:15:00AM#)
else
TotalTime = datediff("h",#05/14/2013 9:00:00AM#,#05/14/2013 10:15:00AM#) + 1
End if

In this example it would yield 15 extra minutes so the total time would round to 2 hours.

 

[ifjzettner]

Both works, thank you very much guys.