Storing time in hrs:mins:secs

Question

I want to record the time it takes to complete a certain task and to store this in hrs:mins:secs format so I can perform calculations on this data. I have tried Short Time and Long Time but these are stored as time of day e.g if I want to store that it took 0 hours 35 minutes and 10 seconds, the field show 12:35:10AM. Appreciate any help.

Answer 1

One thing I'd add to the discussion is that Access doesn't really have a native "duration" data type. Date/Time values represent a point in time, not an elapsed interval, which is why something like 00:35:10 ends up displaying as 12:35:10 AM when treated as a clock value. Because of that, another common approach is to store the duration as a Number field containing total seconds. That makes calculations like SUM, AVG, and comparisons very straightforward, and then you simply format it for display (hh:nn:ss) in a query, form, or report.

Another good design pattern is to store StartTime and EndTime and calculate the elapsed time from those when needed. That way you keep the original timestamps and can recompute the duration later if necessary.

Also worth noting: if you store durations as Date/Time values and format them as hh:nn:ss, anything over 24 hours will wrap around, which can lead to confusing results. Storing the raw value (seconds or minutes) avoids that limitation.

LLAP
RR

Answer 2

Elapsed time is not the same thing as clock time. Although that appears, on the surface, to be blatantly obvious, it is often overlooked when trying to handle tasks such as this one.

The built-in Format() options for Short Date, etc., apply only to clock times and can't be used in calculations with elapsed times. Nor can they reliably display elapsed times. That's not their purpose.

That said, therefore, your task is to capture the start time and end time of the process. Then you can use DateDiff() to calculate the elapsed time between them in seconds. Then, using a formula that applies a custom format to that calculated elapsed time, you can display it in a style that is parallel to the standard clock time, as you require.

That needs to be reinforced again. You can display the elapsed time in any way you want. However, using it in other calculations requires that you use, not that formatted display, but the actual value of the elapsed time, probably in seconds.

Simple rule of thumb here. Use Formatting only to display value to human users. Don't fool around with formatting in calculations as it only adds a layer of confusion in that context.

With regard to Date/Time functions, a couple of sources come to mind. Ken Sheriden maintains a download site with a lot of examples.

https://www.dropbox.com/scl/fo/0scigd3r48hx5xrev2jrf/AB0-GMdTgMAO5O1cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&e=2&dl=0

Gustav Brock has a GitHub repo with lots of good Date related code.

https://github.com/GustavBrock/VBA.Date

Check those location for date related samples. One or more will be highly useful to you.

Answer 3

Hi RobJCut

Thank you for reaching out to the Microsoft Q&A Forum. 

Based on your description, I understand that you want to record the time taken to complete a task in hours:minutes:seconds format and still be able to perform calculations on it. 

If your duration values are less than 24 hours, you might want to try the approach suggested by Q&A Assist. In this case, you can keep the field as Date/Time and simply change the display format: 

-Open the table in Design View. 

-Select the Date/Time field where the duration is stored. 

-In Field Properties > Format, enter: hh:nn:ss  This will display values such as 00:35:10 instead of 12:35:10 AM, while still allowing you to perform calculations with the field.

However, if your durations may be greater than 24 hours, the hh:nn:ss format will wrap after 24 hours. In that case, another option is to keep the duration as text (for example 30:15:00) and convert it to seconds in a query when doing calculations.

For example, you can use a query expression like the following to calculate and display the total duration.

TotalTime:

Format(
Int(
Sum(
Val(Left([Input],InStr([Input],":")-1))*3600 +
Val(Mid([Input],InStr([Input],":")+1,2))*60 +
Val(Right([Input],2))
)/3600),"00")
& ":" &
Format(
Int(
(Sum(
Val(Left([Input],InStr([Input],":")-1))*3600 +
Val(Mid([Input],InStr([Input],":")+1,2))*60 +
Val(Right([Input],2))
) Mod 3600)/60),"00")
& ":" &
Format(
Sum(
Val(Left([Input],InStr([Input],":")-1))*3600 +
Val(Mid([Input],InStr([Input],":")+1,2))*60 +
Val(Right([Input],2))
) Mod 60,"00")

This converts the hours:minutes:seconds value into seconds for calculation and then formats the result back to hours:minutes:seconds when displayed, allowing durations longer than 24 hours to be added and displayed in the same format.

I hope this helps. 

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Store the duration as a Date/Time value (which is actually a fraction of a day) and use a custom format so it displays as elapsed time rather than a time of day.

Access stores Date/Time as a double where the decimal part is the fraction of a 24‑hour day. For example, 0.5 = 12 hours, 0.25 = 6 hours, etc. A duration of 0 hours 35 minutes 10 seconds is:

35 minutes 10 seconds = 35×60 + 10 = 2110 seconds
Fraction of a day = 2110 / (24×60×60) ≈ 0.0244213

So to work with durations:

1. Store the duration as a Date/Time field containing the fraction of a day (e.g. by calculation in a query or VBA).
2. Apply a custom format to that field or control, such as:
   • hh:nn:ss to show hours:minutes:seconds (this will wrap after 24 hours), or
   • use a user-defined function to format intervals longer than 24 hours, as recommended when “you calculate time intervals greater than 24 hours”.

Because Access always treats Date/Time as date+time, a value of 0:35:10 will display as 12:35:10 AM if you use a time-of-day format. Using a custom format focused on the time portion allows calculations (add, subtract, compare) to work correctly while displaying the value as a duration.

---

References:

• Calculate elapsed time
• How to store, calculate, and compare Date/Time data in Microsoft Access
• Format a date and time field