Add More Than 24 Hours

  • Thread starter Thread starter Tom Scanlin
  • Start date Start date
T

Tom Scanlin

I have a workout log, tracking hh:mm [RideTime], time spent on a bike. Since
each ride is <24 hours, the format works great for data entry. It doesn't
work well for reports since a sum of [RideTime] soon increases to over 24
hours. For example, twenty-five hours and 30 minutes shows as 01:25.

I would be ecstatic if 25:30 would show as 01:01:30, happier still if it
showed as 25:30. Can anyone help?

How do I format/code to show over 24 hours?

Thanks
Tom S
 
I have a workout log, tracking hh:mm [RideTime], time spent on a bike. Since
each ride is <24 hours, the format works great for data entry. It doesn't
work well for reports since a sum of [RideTime] soon increases to over 24
hours. For example, twenty-five hours and 30 minutes shows as 01:25.

I would be ecstatic if 25:30 would show as 01:01:30, happier still if it
showed as 25:30. Can anyone help?

How do I format/code to show over 24 hours?
Click to expand...

Well... by not using a Date/Time field, pretty much. A Date/Time isn't
really meant for durations; it is a Double Float count of days and
fractions of a day since midnight, December 30, 1899, so 25:30 is
actually #12/31/1899 01:30:00#, and (unlike in Excel) there is no
format for hours over 24.

I'd suggest storing RideTime as a Long Integer count of minutes - e.g.
a 4 hour 30 minute ride would be stored as 270. You can display this
in hh:nn format with an expression

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

To do the data entry you may want to use two unbound textboxes on a
Form for hours and minutes, with a bit of code in the Form's
BeforeUpdate event to combine them into RideTime:

Private Sub Form_BeforeUpdate(Cancel as Integer)
<form validation code>
Me!RideTime = 60*NZ(Me!txtRideHours) + NZ(Me!txtRideMin)
 
Thank you John Vinson:

I thought I may have take the type of approach you mentioned. Works well but
I made a small format change:

=[SumOfTotalRideTime]\60 & ":" & Format([SumOfTotalRideTime] Mod 60,"00")

This works well in the summary sections.
Thanks again!
Tom

John Vinson said:
I have a workout log, tracking hh:mm [RideTime], time spent on a bike. Since
each ride is <24 hours, the format works great for data entry. It doesn't
work well for reports since a sum of [RideTime] soon increases to over 24
hours. For example, twenty-five hours and 30 minutes shows as 01:25.

I would be ecstatic if 25:30 would show as 01:01:30, happier still if it
showed as 25:30. Can anyone help?

How do I format/code to show over 24 hours?
Click to expand...

Well... by not using a Date/Time field, pretty much. A Date/Time isn't
really meant for durations; it is a Double Float count of days and
fractions of a day since midnight, December 30, 1899, so 25:30 is
actually #12/31/1899 01:30:00#, and (unlike in Excel) there is no
format for hours over 24.

I'd suggest storing RideTime as a Long Integer count of minutes - e.g.
a 4 hour 30 minute ride would be stored as 270. You can display this
in hh:nn format with an expression

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

To do the data entry you may want to use two unbound textboxes on a
Form for hours and minutes, with a bit of code in the Form's
BeforeUpdate event to combine them into RideTime:

Private Sub Form_BeforeUpdate(Cancel as Integer)
<form validation code>
Me!RideTime = 60*NZ(Me!txtRideHours) + NZ(Me!txtRideMin)
Click to expand...
 
Back
Top