**”**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. (

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.The

One half day = 12 hours, stored as 0.5.

1.5 days = 36 hours, stored as 1.5.The whole number portion is derived using

The decimal portion is derived using

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

The whole number portion (1) is a single day, equal to 24 hours.For the record,

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.

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")The same process repeated for 65 hours, 16 minutes, 48 seconds (simply add one more Format() function to the end for seconds):

(Int(1.5)*24)+Format(1.5,"hh") & ":" & Format(1.5,"nn")

(1*24)+12 & ":" & 00

24+12 & ":" & 00

36 & ":" & 00

36:00

(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