Round Short Time

[Newbie2012]

Hello,

I'm having a problem trying to add my two fields, WTime1 and WTime2 to calculate a total SumTime:

The expressions I'm using are:

• WTime1: Format([Start Depot]-1-[Depart Depot],"Short Time")
• WTime2: Format([Arrive Job]-1-[Depart Job],"Short Time")

Also:

• It needs to calculate past midnight
• I would also like WTime1 and WTime2 to be rounded to the nearest 5 minutes, for example, WTime1 at 00:33 should be rounded to 00:35

Can someone help me out here and why I'm getting an #Error?

Thank you Kindly

 

[essaytee]

Dealing with time calculations is a real pain. Anyway, the DateDiff() function should help.

From the immediate window:

? datediff("n", now(),#8/apr/2019 01:20#)
 489

So that's a time past midnight and the result is in minutes.

I'll get back to you about rounding to 5 mins, my mind's gone blank.

 

[essaytee]

Actually, to make it clearer, won't use now() in the function:

? datediff("n", #7/apr/2019 23:00#, #8/apr/2019 01:00#)
 120

As an aside, if I had of entered the date as '8/4/2019' the system assumes it's a US date, again, a pain in the a..... always catches me out.

You can use only the time portion but the 'past midnight' will not be accounted for.

? datediff("n","23:00", "01:00")
-1320

Edit/Update:
To round to 5 mins, try the following:

? round(123/5,0) * 5
 125 
? round(121/5,0) * 5
 120

 

[Gasman]

Personally I always round to 6 minutes, so I can get 5.1, 5.9 hours etc.