Time Format ? (Y2K)

Topic

New Reply

In Excel there is a format option that returns the total number of hours, minutes and seconds, rather than what the time is, i.e. [h]:mm:ss. I normally use this when I need to know the difference between two dates and times, e.g. 09/06/06 11:03:35 minus 09/01/06 14:38:30 returns 01/04/1900 8:25:05 PM, which is formatted as 116:25:05, because the difference between the two dates and times is more than 24 hours.

I imported data from an execel spreadsheet in to Access with a column of this data and format. In Access, I used format h:nn:ss in the table for this column, which works fine for time differences that are less than 24 hours; however, for values greater than 24, the total number of hours, minutes and seconds is not returned correctly (or at least how it’s returned in Excel), e.g. 01/04/1900 8:25:05 PM is returned as 20:25:05, rather than 116:25:05.

Any help provided would be appreciated.

Scott

Reply | Quote

Replies

It would have been nice if Access had a cumulative time format like Excel, but unfortunately it doesn’t.

If you can live with decimal hours, e.g. 116.5 instead of 116:30, you can use an expression like

=24*([DateTime1]-[DateTime2])

Otherwise, you’ll have to compute the hours, minutes and seconds separately:

=Int(24*([DateTime1]-[DateTime2]))

for the hours,

=Int(24*60*([DateTime1]-[DateTime2]) Mod 60)

for the minutes, and

=Int(24*60*60*([DateTime1]-[DateTime2]) Mod 60)

for the seconds. You can then concatenate these again in a string value, if you wish.

Reply | Quote