By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,204 Members | 1,360 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,204 IT Pros & Developers. It's quick & easy.

How to format an amount of time > 24hr

Expert 5K+
P: 8,434
Hi all.

I have a query which calculates the difference between two date/time fields. This returns a number which is, of course, useless without formatting (“I couldn’t believe it took 1.15740767796524E-05 to do that”). I have formatted this as "hh:nn:ss", and this works OK. But it clocks over at 24 hours, which effectively renders it useless for my purposes. Using "hhh:nn:ss" doesn't work. I forget the details, but it produced nonsense.

So, how can I format an amount of time in a way that will still work when it goes over 24 hours? In fact, the hours could even go to three digits, though rarely.
Oct 26 '06 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,494
You come up with some good ones Killer.
Try "0 hh:nn:ss"

This does, of course, mean that ALL times are shown including a day indicator.
Oct 26 '06 #2

Expert 5K+
P: 8,434
You come up with some good ones Killer.
Try "0 hh:nn:ss"
This does, of course, mean that ALL times are shown including a day indicator.
Thanks, I'll give it a shot.
Oct 26 '06 #3

Andrew Thackray
P: 76
the formatting won't work if you used the datediff to get the difference as it returns an integer result. How to do it de[ends on the smallest unit of difference you want to report on. If, for example you want to show hors & minutes the following code works
Oct 27 '06 #4

Andrew Thackray
P: 76
the formatting won't work if you used the datediff to get the difference as it returns an integer result. How to do it de[ends on the smallest unit of difference you want to report on. If, for example you want to show hors & minutes the following code works

Dim Diffierence as long
Dim FormattedTime as string

Difference = datediff("n",Firstdate,SecondDate)
formattedtime = int(difference/60) & " Hrs " & difference mod 60 & " Min

if you want days, hours & minutes the code would be

formattedtime = int(diference/1440) & " Days " & int((difference mod 1440)/60) & " Hrs " & int((difference mod 1440) mod 60) & " Min"

This code will give you the time diffrenece in days, hrs & minutes for all dates
Oct 27 '06 #5

Expert 5K+
P: 8,434
Thanks for that, Andrew. But I am simply subtracting two date/time fields. The result is a tiny number, see original post for a sample. I am happy with this result, as I often copy/paste the result set to Excel for graphing and so on, and apply the format [h]:mm:ss there, which gives me as many digits as I need for the hours.

I just prefer to have it formatted as an amount of time in Access too, but the hh:nn:ss I was using there is not quite right, and it doesn't recognise the one from Excel.
Oct 27 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.