Strangeness of Time Formatting
July 07, 2022 - by Bill Jelen
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.
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.
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.
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.
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].
This article is an excerpt from Power Excel With MrExcel
Title photo by Simon Berger on Unsplash