Strangeness of Time Formatting


July 07, 2022 - by

Strangeness of Time Formatting

Problem: Something is strange with time formatting. I can’t total my time sheet to show 40 hours.

Strategy: Do you remember when you had a date and you formatted it to show only one element of the date? All of the cells in column C contain the exact same value, but they have a different numeric format.


In cell C4, you are asking Excel to show you only the day, so it gives you a 3. That is exactly what you asked for. You didn’t ask to see years or months, so it did not include that value.

A cell containing June 3 2011 16:25 can be made to be displayed as six different values using cusom number formatting:
Jun for June
3 for third of the month
2011 for year
16 for Hour of the Day
16:25 for Hours : Minutes
or 40967.68432 is formatted as general.
Figure 560. Control the display of a value using format codes.

In contrast, consider the following time sheet.



Everyone looks at cell H15 and says that something is wrong. It should be 40 hours, not 16 hours.

But Excel is doing the same thing here that it did back in Figure 560. You formatted H15 with the H:MM format, so Excel threw out the date portion of the value. Think about it. 40 hours is really 1 day and 16 hours. All that you are seeing in H15 is the 16 hours. You didn’t ask to see the day.

Since time tracking is a common activity in Excel, there must be a solution.

Five cells contain times. Although they add up to 40 hours, the total at the bottom is showing only 16 hours.
Figure 561. The payroll department will save on salary expense here.

There is, but it is not easy to figure out.

Select cell H15. Use Ctrl+One to Format Cells. Select the Time category. Scroll down until you see the time format with 37:30:55.

The solution is to find the Time format that shows 37:30:55 in the sample box.
Figure 562. Choose 37:30:50 to display hours in excess of one day.

This will show your time as 40:00:00.

In reality, you have more flexibility if you use the Custom category. Choose the 37:30:50 and then click on Custom. You will see the code is [H]:MM:SS. The square brackets are the code to tell Excel that you want to see all hours, not just the hours in excess of whole days.

WIth the proper formatting, the five cells total to 40 hours.
Figure 563. Forty hours.

You can extrapolate the following custom codes:

[H]:MM is the format you want for the time sheet.

You can also display the absolute number of minutes or seconds using formats of [D] or [S].

A format of h:mm AM shows 3:00 AM. The same number can display 180 with a number format of [M] or 10800 with a number format of [S].
Figure 564. 3AM is 180 minutes past midnight.

This article is an excerpt from Power Excel With MrExcel

Title photo by Simon Berger on Unsplash