decimel to short time

[default105]

I have a form that when I original made it, it calculated elapsedtime,
elapsedshorttime and timedecimalequiv based on two dates usings the format ie
"01/02/2005 06:30:00 AM" ([dateopen] and [dateclosed]). This added a nice
compliment to form and displayed the elapsedtime ( 1 day, 4 hours, 32
minutes, 00 seconds) and elapsedshorttime(28:32:00) and timedecequiv
(28.53333). Now I was asked to make a make a mod to the form so the user can
put in a decimal number and if the the item in question was not down the
whole time between the two fields listed previously. Since the user will be
listing the the time in decimal format, I would like to convert it to
shorttime format. I have been able to do it will this code but I have a
limitation to it. With the input 25.5 it displays (01:30:00).

Function CreateElapsedShortTime(Interval)
Dim totalhours As String

totalhours = Format$(Interval / 24, "hh:mm:ss")
CreateElapsedShortTime = totalhours

End Function

How do I allow it to calculate past 24 hours so with 25.5 it will display
25:30:00?

 

[John Vinson]

How do I allow it to calculate past 24 hours so with 25.5 it will display
25:30:00?

The problem is that an Access Date/Time is stored internally as a
Double Float count of days since midnight, December 30, 1899. Times
are the fractional portion. Thus 25 hours 30 minutes - as a duration -
corresponds to 1:30am on December 31, 1899 (and formatting it as you
have done displays only that time portion). Unlike in Excel, there is
no format that displays hours over 24.

Instead, use integer division and modulo arithmatic:

[Interval] \ 24 & Format([Interval] \ 1440 MOD 60, ":00") &
Format([Interval] \ 86400 MOD 60, ":00")

John W. Vinson[MVP]

 

[default105]

I tried that but all I get it the result of 01:00:00. Sorry this has me
baffled

How do I allow it to calculate past 24 hours so with 25.5 it will display
25:30:00?

The problem is that an Access Date/Time is stored internally as a
Double Float count of days since midnight, December 30, 1899. Times
are the fractional portion. Thus 25 hours 30 minutes - as a duration -
corresponds to 1:30am on December 31, 1899 (and formatting it as you
have done displays only that time portion). Unlike in Excel, there is
no format that displays hours over 24.

Instead, use integer division and modulo arithmatic:

[Interval] \ 24 & Format([Interval] \ 1440 MOD 60, ":00") &
Format([Interval] \ 86400 MOD 60, ":00")

John W. Vinson[MVP]

 

[default105]

I tried your suggestion but had very little success, I did however from
reviewing what you had written, I came up with this

Function CreateElapsedShortTime(Interval)

Dim totalhours, actualhours, totalsecs, actualsecs
totalhours = (Interval * 60)
actualhours = totalhours \ 60 & ":" & Format$(totalhours Mod 60, "00")
totalsecs = (Interval * 86400)
actualsecs = Format$((totalsecs) Mod 60, "00")
CreateElapsedShortTime = actualhours & ":" & actualsecs

End Function

I don't know if it is the proper way to do this but it works out when I
check it in the immediate window against this code

Function GetElapsedShortTime(Interval)

Dim totalhours As Long

totalhours = Int(CSng(Interval * 24))
GetElapsedShortTime = totalhours & ":" & Format(Interval, "nn:ss")

End Function

If there is a better way or I have just screwed up please let me know.
Thanks for your help

How do I allow it to calculate past 24 hours so with 25.5 it will display
25:30:00?

The problem is that an Access Date/Time is stored internally as a
Double Float count of days since midnight, December 30, 1899. Times
are the fractional portion. Thus 25 hours 30 minutes - as a duration -
corresponds to 1:30am on December 31, 1899 (and formatting it as you
have done displays only that time portion). Unlike in Excel, there is
no format that displays hours over 24.

Instead, use integer division and modulo arithmatic:

[Interval] \ 24 & Format([Interval] \ 1440 MOD 60, ":00") &
Format([Interval] \ 86400 MOD 60, ":00")

John W. Vinson[MVP]