Calculating total minutes, wrong total when stop time is next day

[mvital]

Hello Everyone!

I have two data/time fields, "start time" and "stop time." I want to calculate total minutes. The problem I am having is that when the stop time occurs the next day the total minutes calculated is incorrect. For example,

Start Time: 10:00 a.m.
Stop Time: 1:00 a.m.

Below is the calculation:

(Hour([NEW]![Stop Time]-[NEW]![Start Time])*60)+Minute([NEW]![Stop Time]-[NEW]![Start Time])

When I run the update query, for the example above I get 540 minutes (9 hours) when it should be 15 hours or 900 minutes.

What am I doing wrong? Using military time.

thanks in advance,

Maribel

 

[Golom]

Just use datediff

DateDiff ("n", [Start Time], [Stop Time])

 

[mvital]

Well, it did not work. It's still doing the same thing. Keep in mind that there is no date, it is only a time field. Basically, subtracking the start time from the stop time and getting the total minutes.

Not sure....


thanks!

mvital

 

[SkipVought]

Hi,

Then just add a day to the stop time, cuz there's not way of knowing if its MORE than one day.

DateDiff ("n", [Start Time], [Stop Time]+iif([Start Time]> [Stop Time],1,0))

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[PHV]

And what about this ?
1440 * IIf([Start Time]<[Stop Time], [Stop Time]-[Start Time], 1+[Start Time]-[Stop Time])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mvital]

Thank you both for your help. Kip, your solution worked beautifully, can't believe I had not thought about that.

Thanks again!

Have a good day!

mvital