Code Example: Time in MS Access

“How do I display a time over 24 hours in MS Access?”

I’ve run into this problem a lot over the years, and I always end up having to Google up the answer. In doing so, I’ve seen numerous messages boards with the same question, yet frequently no answer, which leads to more digging and time wasted.

Well, here’s a solution that works…

(Int([TimeValue])*24)+Format([TimeValue],"hh") & ":" & Format([TimeValue],"nn")

...And it’s just that simple, folks. ([TimeValue] is a Date/Time variable holding your time.)

For those wondering, this is how it works:

Access stores date/time values in a format that can be broken down into a separate date and time amount, separated by the decimal point. Anything on the left-hand portion (the whole number part) relates to whole days or a date, while everything on the right-hand portion (the decimal part) relates to a partial day or a time.

1 day = 24 hours, stored as 1.0.
One half day = 12 hours, stored as 0.5.

The Int() function returns the integer or whole number part of a number (i.e. strips the decimal portion). Using this, we can also extract the decimal portion. The second step here is not necessary, as the Format() function (see below) will handle the decimal portion the same way, regardless.

1.5 days = 36 hours, stored as 1.5.

The whole number portion is derived using Int(1.5) = 1 (equal to 1 day/24 hours).
The decimal portion is derived using 1.5 – Int(1.5) = 1.5 – 1 = 0.5 (equal to one half day/12 hours).

Once we have identified each portion, it’s simply a matter of converting the whole number (days) to hours, and adding that to the partial day hours. This is done using Access’ internal Format() function as well as some simple math.

The whole number portion (1) is a single day, equal to 24 hours.
To convert this, we simply multiply our number by 24 (1 * 24 = 24).
We can split the decimal portion into hours and minutes with the Format() function as follows.
Format(1.5,"hh:nn") = 12:00, Format(1.5,"hh") = 12, Format(1.5,"nn") = 00.

For the record, Format(1.5,"hh:nn") will result in the same number as Format(0.5,"hh:nn") and Format(132.5,"hh:nn"). The value of the hours returned is always under 24, therefore it is not necessary to split the decimal portion out.
As we can extract the numbers in this way, so can we also add them back together, giving us our total hours. Here’s our original function, with the example of 1.5 (one and one half days/36 hours).

(Int([TimeValue])*24)+Format([TimeValue],"hh") & ":" & Format([TimeValue],"nn")
(Int(1.5)*24)+Format(1.5,"hh") & ":" & Format(1.5,"nn")
(1*24)+12 & ":" & 00
24+12 & ":" & 00
36 & ":" & 00
36:00

The same process repeated for 65 hours, 16 minutes, 48 seconds (simply add one more Format() function to the end for seconds):

(Int([TimeValue])*24)+Format([TimeValue],"hh") & ":" & Format([TimeValue],"nn") & ":" & Format([TimeValue],"ss")
(Int(2.72)*24)+Format(2.72,"hh") & ":" & Format(2.72,"nn") & ":" & Format(2.72,"ss")
(2*24)+17 & ":" & 16 & ":" & 48
48+17 & ":" & 16 & ":" & 48
65 & ":" & 16 & ":" & 48
65:16:48