Excel Strangeness of Time Formatting

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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.

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

Figure 569 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.

Figure 570 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].

SHARE

Additional Info

Spread the Word Excel

MrExcel.com & related websites debuted on November 21, 1998.

ExcelArticles.com provides examples of Formulas, Functions and Visual Basic procedures for illustration only,
without warranty either expressed or implied, including but not limited to the implied warranties of
merchantability and/or fitness for a particular purpose. The Formulas, Functions and Visual Basic procedures
on this web site are provided "as is" and we do not guarantee that they can be used in all situations.
This site contains affiliate links. Any affiliate commissions that we earn when you click a link to Amazon
or other sites is reinvested in keeping MrExcel.com and ExcelArticles.com running. You can earn a commission for
sales leads that you send to us by joining our affiliate program. View my Privacy Policy.

Excel ® is a registered trademark of the Microsoft Corporation.
MrExcel ® is a registered trademark of Tickling Keys, Inc.