Time calculations problem 1

[rjnitke]

I have a database at work to calculate surgical time lengths in minuts. My problem is that when the elapsed time goes past midnight, I get a negative total. The db is reading the two times on the same day. For example: Surgical date 1 Sep 03, start time 2300, stop time 0200. I need this to calculate as 120 minutes, but it comes out as -4260. What can I do?

 

[VicLavigne]

"I put the expression:
IIf([EndTime]>[Cut],[EndTime]-[Cut],(TimeValue("23:59&quot+[EndTime])-[Cut]+TimeValue("00:01&quot)
In a field on my query. The query draws the numbers from a table where date fields are formated as 'general date'. When I run my report, I get a decimal number like this:"

I have my field formatted as Short Time, not General Date.
That gives an output that looks like 06:42 if the expected calculation were six hours 42 minutes. And of course, I'm assuming you substituted your own table names for the place where I have [Cut] and [End Time].

 

[rjnitke]

Thank you thank you thank you. Thanks to Bob and Vic especially. I used a little bit of what each of you suggested and had a little brainstorm of my own. The end result which acheived the desired effect was this:

Total Surgical Time: IIf([Time Out]>[Time in OR],DateDiff("n",[Time In OR],[Time Out]),DateDiff("n",[TimeInPMN],[TimeOutPMN]))

I got the [TimeInPMN] and [TimeOutPMN] by creating two new fields:
TimeInPMN: [Time In OR]+TimeValue("00:01&quot
and
TimeOutPMN: [Time Out]+TimeValue("23:59&quot

This worked beautifully. Looking back it seems so simple. Again, thank you for all your help.