Excel versus Access Time formats???

  • Thread starter Thread starter Gamliel Beyderman
  • Start date Start date
G

Gamliel Beyderman

B"H

Hi,

When it comes to displaying time, Excel has a format that
looks like this: [h]:mm:ss. You can see it in the Custom
category of Number tab (with the Format Cells menu). For
example, a date such as "2/22/1900 5:10:13 PM" can be
displayed as "1289:10:13." [h] here means the elapsed
time.

I don't seem to find a straightforward way to display the
date in the same format in Access. Is there such thing as
the elapsed time function?

Thanks! Gamliel

Please CC the response to my email too if possible.
 
You'll have to write a function to do it.

In Access, date/times are intended strictly as timestamps: there's no true
facility to store elapsed times. This is because under the covers Access
stores date/times as 8 byte floating point numbers, where the integer part
represents the date as the number of days relative to 30 Dec, 1899, and the
decimal part represents the time as a fraction of a day. If you strictly
store something like 8:00:00, Access will store this as 0.333333, which
means 8:00 AM on 30 Dec, 1899 to Access. When you add a number of times
together, so that you've now got 1.25 (for example), this means 6:00 AM on
31 Dec, 1899, NOT 30:00:00.

Something like the following will allow you to format the value, though:

Function FormatTimeDurations(TimeValue As Date) As String

FormatTimeDurations = Format$(24 * Int(TimeValue) + Hour(TimeValue),
"00") & _
":" & Format$(Minute(TimeValue), "00")
& ":" & _
Format$(Second(TimeValue), "00")

End Function
 
Back
Top