Results 1 to 2 of 2
Thread: Time Format ? (Y2K)
2006-09-29, 16:10 #1
- Join Date
- Feb 2003
- Thanked 0 Times in 0 Posts
Time Format ? (Y2K)
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.
2006-09-29, 16:21 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 Posts
Re: Time Format ? (Y2K)
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
Otherwise, you'll have to compute the hours, minutes and seconds separately:
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.