Elapsed Time Format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave S - 03 Mar 2007 03:18 GMT

I have seen numerous suggestions for formatting elapsed time formats into
hours and minutes such as:

=[Minutes] \ 60 & Format([Minutes] Mod 60, "\:00")

I need to calculate my elapsed times (between two fields in a database) down
to the second. I already have the proper DateDiff("s", startfield, endfield)
worked out. It is returning the proper number of seconds elapsed.

I really would like to then display this information on forms, reports, etc.
as hh:mm:ss format. the format shown above works ok up to 59 minutes, and 59
seconds but then shows 60, 61, 62, (even into 3 and four digit minutes) etc.
in the minutes.

For example: What format can I use to show 01:02:15 (i.e., 1 hour 2 minutes
15 seconds) from the calculation in the DateDiff function that returns 3735
seconds??

thanks
dave

Reply to this Message

John W. Vinson - 03 Mar 2007 05:13 GMT

>For example: What format can I use to show 01:02:15 (i.e., 1 hour 2 minutes
>15 seconds) from the calculation in the DateDiff function that returns 3735
>seconds??

[duration] \ 3600 & Format(([duration] \ 60) MOD 60, "\:00") &
Format([duration] MOD 60, "\:00")

John W. Vinson [MVP]

Reply to this Message

Dave S - 03 Mar 2007 17:59 GMT

Thanks John, that worked great in my forms and reports. One other issue I am
now having is that I want to summarize a number of elapsed times on a report.
I put the following formula in the control source for a text box in the
group footer:

=Sum([ElapsedTime]\3600 & Format(([ElapsedTime]\60) Mod 60,"\:00") &
Format([ElapsedTime] Mod 60,"\:00"))

When I run the report I get an error message about the expression being
typed wrong or being too complex to be evaluated. If I put the formula:

=Sum([ElapsedTime])

I get the correct number of seconds. Again, I want to display this as
hh:mm:ss but the total for either the report or the group in the report.

For example, one section has a total sum of 24421 seconds of elapsed time.
What do I need to do to get the resulting display in the group footer to show
06:47:01 (i.e., 6 hours 47 minutes and 01 seconds).

Thanks.
Dave Schoeffel

> >For example: What format can I use to show 01:02:15 (i.e., 1 hour 2 minutes
> >15 seconds) from the calculation in the DateDiff function that returns 3735
[quoted text clipped - 4 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 03 Mar 2007 21:46 GMT

>=Sum([ElapsedTime]\3600 & Format(([ElapsedTime]\60) Mod 60,"\:00") &
>Format([ElapsedTime] Mod 60,"\:00"))
[quoted text clipped - 6 lines]
>I get the correct number of seconds. Again, I want to display this as
>hh:mm:ss but the total for either the report or the group in the report.

You need to replaced the last two [ElapsedTime] references with

Sum([ElapsedTime])

It's not the elapsed time that you want to display formatted - it's
the SUM that you want to display!

Don't worry too much about including the Sum() three times - I'm
pretty sure that Access is smart enough to just sum it once and use
the same result; if not... cpu cycles are cheap these days...

John W. Vinson [MVP]

Reply to this Message