Short Time showing hours over 24

  • Thread starter Thread starter Brian
  • Start date Start date
B

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?
 
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?
 
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?
Click to expand...

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]
 
Brian said:
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?
Click to expand...


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.
 
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.
Click to expand...

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

Thanks Marshall!

John W. Vinson [MVP]
 
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.

John W. Vinson said:
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?
Click to expand...

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]
Click to expand...
 
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
 
Back
Top