Query Calculation Format

[erummel]

I've written an expression within a query to do a time/date calculation and report the result with a bunch of other data I've pulled. The expression works great and does exactly what I ask of it. However, my superiors would like the data returned in a slightly different format.

My expression is thus:

MTTR: Int([Outage End]-[Outage Start]) & ":" & Format([Outage End]-[Outage Start],"Short Time")

So, for an Outage Start value of 07/02/2014 09:42 and and Outage End value of 07/05/2014 02:30 it correctly returns 2:16:48- as in 2 days, 16 hours and 48 minutes.

I'd like to be able to have the output in h:mm format- so for the example above, it would be 64:48- 64 hours and 48 minutes.

I have racked my brain trying to fiddle with the Short Time reference in the expression above to no avail. I'd prefer not to have to spend a lot of time in excel manually converting each value into the correct format. In Excel, I format the fields with this data in it as

[h]:mm

. Is there something analogous in Access for this?

Thanks for any help!

Best,

Eric

 

[MarkK]

Check out the DateDiff() function, which helps you calculate the difference between two date/time values in different units of time. So find the DateDiff() in minutes between your data, divide by 60 to get hours, and the remainder stays in minutes.

 

[erummel]

So, using DateDiff, I have come up with:

MTTR: DateDiff('n',[Outage Start],[Outage End])/60

The result for a record which should be 2:40 is returned as 2.666666666666667. When I apply the h:nn format to the field, the result is returned as 16:00.

Same thing happens when I export to excel and use my [h]:mm format on the field.

Any ideas on how to fix the formatting?

 

[spikepl]

You need to do what MarkK wrote EXACTLY! Timespans over 24 hours cannot be displayed using Access date variables/formats but you have to construct the string (note: string, not Date-variable) yourself (2.6667 is correct, expressed in decimal values - 2 hours and two thirds of one hour)

 

[erummel]

So if I am understanding correctly, my expression is returning exactly what it should: 2.6666667 hours. It sounds as if I may out of luck as far as an easy way to convert to the format I need of hours and minutes by the dreaded 'Access cannot handle timespans over 24 hours' issue. I usually have several of these per month.

 

[MarkK]

"Date Zero" in Access is . . .

? format(0, "Long Date")

. . . 30-Dec-1899. The date format "h:nn" doesn't show hours over 24 because if it did, it would also have to, typically, show all the hours since 1899.

DateDiff(), by contrast, is elapsed time--not a date--in the unit of your choice. Minutes is good if you want to show hours and minutes, but then you need calculate the hours . . .

    hrs = Int(TotalMinutes / 60)

. . . and extract them from the minutes . . .

    mins = TotalMinutes - 60 * hrs

Hope this helps,

 

[erummel]

I know this sounds dumb, but where would I put those additional expressions?

I currently have this as my expression:

MTTR: DateDiff('n',[Outage Start],[Outage End])/60

Would I add those two lines to the end of that? In a different field?