Calculating Elapsed Time Spanning Midnight 2

[EricRN]

I am writing a report which will calculate the elapsed time of surgical cases. The problem is, some of the cases span accross midnight. This causes my field to be show up as almost 24 hours in minutes (ex. -1340).

Here is how I have it set up:

I have In Time and Out Time fields that are formatted as time fields in the database. I created a formula field that subtracts the In Time field from the Out Time field then multiplies the answer by 1440 to get the total minutes. I can then sum the minutes at the end of the report. I have a group footer set up with a summary of minutes. For one particular group, I ended up with -471 minutes. That's when I checked the detail and saw that with those cases that span the midnight hour, I was getting -1340 (give or take) minutes.

How can I fix this? I need to be able to report out the amount of total minutes by group, and total minutes for the report.

Thanks!

Eric

 

[csm2]

If you have a date field associated with time field, you may be able to use DateDiff function.
Or add 24 hours to OutTime and then subtract InTime.

 

[EricRN]

Unfortunately, it's a straight time field. I had tried the DateDiff function and ended up with the same numbers.

 

[HowardHammerman]

Check out:

faq149-1215

on this forum for some good ideas. Howard Hammerman,

Crystal Reports training, consulting, books, training material, software, and support. Scheduled training in 8 cities.
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[EricRN]

Thanks Howard! Since I have seperate fields for date and time (exported from a Btrieve program...don't ask, it's too painful!), I guess I'll have to combine those fields for both the surgery start and surgery end, then plug them into your formula.


Eric

 

[MarkSweetland]

I use a formula that computes the number of minutes elapsed between two times (in 24 hour format) even if the times pass over midnight:

In Basic syntax...

    Dim intStartMinSince0 as Number
    Dim intStopMinSince0 As Number
    Dim intTotalMin As Number
    Dim intMinPast As Number
    Dim intHrs As Number
    Dim intSpan1 As Number
    Dim intSpan2 As Number
    Dim intStartHr as Number
    Dim intStopHr as Number

    intStartHr={IN_TIME}
    intStopHr={OUT_TIME}

    If intStopHr < intStartHr Then ' assume over midnight span
        ' compute minutes until midnight
        intMinPast = ((24 - Int(intStartHr / 100)) * 60) - (intStartHr Mod 100)
        'reset start to midnight
        intStartHr = 0
    End If
    ' add any prior day minutes to computation
    intMinPast = intMinPast + (intStopHr Mod 100)  ' to make zero based  = +1
    intStartMinSince0 = (((intStartHr \ 100) * 60)) + (intStartHr Mod 100)
    intHrs = intStopHr \ 100
    intStopMinSince0 = intHrs * 60
    intStopMinSince0 = intStopMinSince0 + intMinPast
    intTotalMin = intStopMinSince0 - intStartMinSince0

    Formula = cstr(intTotalMin) + &quot; minutes.&quot;

Mark

 

[EricRN]

Thanks Mark,

That looks like it may work better for me since my date fields and time fields are seperate.


Eric