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.
-
Control the display of a value using format codes.
In cell C4, you are asking Excel to show you only the date, 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.
- The payroll department will save on salary expense here.
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 Fig 575. 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.
- 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.
- 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].
- 3AM is 180 minutes past midnight.