Short Time showing hours over 24

[Brian]

I am adding daily times (hh:nn) to arrive at weekly, monthly, or yearly
totals, depending on a date range selected by a user at output time. I want
to display the total in hh:nn (Short Time) style, except that I want to have
the "hh" portion show total hours; Short Time is modal 24 hours.

Is there a number format that will show hh:nn but will show hours over 24,
or do I have to write a function to convert it to a string?

 

[Allen Browne]

You cannot use the built-in formats to achieve that.

It can be done with DateDiff(), using "n" for minutes ("m" is for months),
but it might be easier to store the field in minutes.

More info:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

 

[Brian]

Thanks, Allen. Since I rather suspected there was no built-in functionality
to do this, I broke down & just wrote this function after my post:

Public Function DisplayHoursMinutes(TimeIn As Double) As String
Dim TimeTemp As Double
Dim HoursTemp As Variant
Dim MinutesTemp As Variant
TimeTemp = TimeIn * 24 'convert to hours
HoursTemp = Int(TimeTemp) 'extract # of hours
If HoursTemp = 0 Then HoursTemp = "00"
TimeTemp = TimeTemp - HoursTemp 'remainder
TimeTemp = TimeTemp * 60
MinutesTemp = Round(TimeTemp, 0)
If TimeTemp < 10 Then MinutesTemp = "0" & MinutesTemp 'leading 0
DisplayHoursMinutes = HoursTemp & ":" & MinutesTemp
End Function

This is actually on a report, where the field in question is a footer sum of
detail times that are derived as DateDiff from start/stop times in the
report's underlying query.

Since it is in a report only, and exists in a terminal position (i.e. no
other controls depend on it), the string output result will not cause any
data-related problems, and it works just fine. Is my solution more work than
it needs to be?

 

[John W. Vinson]

Is there a number format that will show hh:nn but will show hours over 24,
or do I have to write a function to convert it to a string?

No.

I'd suggest storing the duration in a Long Integer count of minutes - e.g.
1500 = 25 hours. You can then display it in hh:nn format with an expression

[Duration]\60 & Format([Duration] MOD 60, "\:00")


John W. Vinson [MVP]

 

[Marshall Barton]

I am adding daily times (hh:nn) to arrive at weekly, monthly, or yearly
totals, depending on a date range selected by a user at output time. I want
to display the total in hh:nn (Short Time) style, except that I want to have
the "hh" portion show total hours; Short Time is modal 24 hours.

Is there a number format that will show hh:nn but will show hours over 24,
or do I have to write a function to convert it to a string?

I totally agree with Allan and John, but if you are stuck
with a field containing date/time values as durations, you
can calculate the desired output string using:

=Format(t*24,"#") & Format(t,":nn")

where t is your total time field/expression.

 

[John W. Vinson]

I totally agree with Allan and John, but if you are stuck
with a field containing date/time values as durations, you
can calculate the desired output string using:

=Format(t*24,"#") & Format(t,":nn")

where t is your total time field/expression.

Clever... and I would NOT have come up with that. <scribble scribble>

Thanks Marshall!

John W. Vinson [MVP]

 

[Allen Browne]

Looks like Marshall has given you a nifty little solution.

In case you need something more, Doug Steele also has a more comprehensive
DateDiff() function:
http://www.accessmvp.com/djsteele/Diff2Dates.html

You have to admit, there's some helpful Access codgers here.

 

[Brian]

Thanks. I ended up implementing Marshall's solution. The duration is not
actually stored anywhere because it is calculated at runtime from beginning &
ending times that are input as hh:nn (24-hour format).

All much better answers than my function.

Is there a number format that will show hh:nn but will show hours over 24,
or do I have to write a function to convert it to a string?

No.

I'd suggest storing the duration in a Long Integer count of minutes - e.g.
1500 = 25 hours. You can then display it in hh:nn format with an expression

[Duration]\60 & Format([Duration] MOD 60, "\:00")


John W. Vinson [MVP]

 

[avacess]

gents,

same subject as above, and a dummy question:

is it possible to insert in a table total hours and minutes in a short time format? i am interested to insert hours more than 24 hours, something like 54 hours and 12 minutes. I would like to insert 54:12. Is this possible?
or I need to insert as minutes: 54*60+12?
I would appreciate your opinion.
Kind regards,
avacess