finding elapsed time

[Dan]

I have a table that gives two sets of times in date/time
format. I need to find the elapsed time between these two
data items AND I need the result to be formatted in total
hours:minutes.

Using DateDiff I have been able to find the total number
of minutes and then divide that number by 60, however that
results in the hours and fraction of hours rather than
hours and minutes.

Any idea on the best way to overcome this problem? Is
DateDiff my best option or is there something else?

Thanks!

 

[Ken Hutson]

You can try this.
Thake an example, say 230 minutes. We know it's 3 hours 50 minutes.
230/60 = 3.833333
subtract the integer portion
3.833333 - 3 = 0.833333
multiply the decimal portion by 60 to get minutes
0.833333*60 = 50
3 hours 50 minutes

 

[John Vinson]

I have a table that gives two sets of times in date/time
format. I need to find the elapsed time between these two
data items AND I need the result to be formatted in total
hours:minutes.

Using DateDiff I have been able to find the total number
of minutes and then divide that number by 60, however that
results in the hours and fraction of hours rather than
hours and minutes.

Any idea on the best way to overcome this problem? Is
DateDiff my best option or is there something else?

Thanks!

You can do some arithmatic operations:

DateDiff("h", [start], [end]) & Format(DateDiff("n", [start], [end])
MOD 60, ":00")

 

[John Spencer (MVP)]

Mr Vinson,

Pardon me, but won't that give you incorrect data in many cases? Since going
from 06:59 to 07:01 would return 1 hour and 2 minutes vice zero hours and 2
minutes? I think you meant something like the following. Which, IF I recall
correctly, I originally got from you.

DateDiff("n", [start], [end])\60 & Format(DateDiff("n", [start], [end]) MOD 60, ":00")

I have a table that gives two sets of times in date/time
format. I need to find the elapsed time between these two
data items AND I need the result to be formatted in total
hours:minutes.

Using DateDiff I have been able to find the total number
of minutes and then divide that number by 60, however that
results in the hours and fraction of hours rather than
hours and minutes.

Any idea on the best way to overcome this problem? Is
DateDiff my best option or is there something else?

Thanks!

You can do some arithmatic operations:

DateDiff("h", [start], [end]) & Format(DateDiff("n", [start], [end])
MOD 60, ":00")

 

[John Vinson]

Mr Vinson,

Pardon me, but won't that give you incorrect data in many cases? Since going
from 06:59 to 07:01 would return 1 hour and 2 minutes vice zero hours and 2
minutes? I think you meant something like the following. Which, IF I recall
correctly, I originally got from you.

DateDiff("n", [start], [end])\60 & Format(DateDiff("n", [start], [end]) MOD 60, ":00")

EEEP!

I clearly needed some more coffee when I posted the first reply.
You're exactly correct, of course; thanks for the good catch!