» Totaling Time Values
CATEGORY - Excel Time
VERSION - All Microsoft Excel Versions
The time format to display hours, minutes and seconds is HH:MM:SS. For example, a time of 14 hours and 56 minutes is displayed as 14:56:00.
Problem:
The default time format does not allow a time value to exceed 24 hours. For example, by entering a time of 28:56:00, the result is 04:56:00.
Solution:
Change the format of the cell by placing brackets around the hour, [HH]:MM:SS. The result is displayed as 28:56:00.
In the screenshot below, cell C5 uses the SUM function in a formatted cell to sum times correctly.
Book Store:
Recommended Books:
- Financial Risk Manager Handbook, Second Edition
- Your First Business Plan: A Simple Question and Answer Format Designed to Help You Write Your Own Plan (3rd Ed)
- Getting to Yes: Negotiating Agreement Without Giving In
- Special Edition Using Microsoft Outlook 2002
- Excel 2002 Power Programming with VBA
- Absolute Beginner's Guide to Microsoft Excel 2002
Timesheet problems????
Chris
This is fine, Ive created a Timesheet with the same formulas but if you have a cell ie: [E5] =C5 the result refers back to numbers, I have a time sheet that has 8 sheets, sheet 1 to 7 is days of the week and sheet 9 is weeks summary which has the total of all worked hours for the week, but when I try to add all the hours together all I get is numbers, it dosn't matter which way try to format the cell. If anybody has a solution please tell me.
Good tip
Viet Pham
Great!
This is helpful tip. Thank you very much.
adding time
kaylie
this tip does not tell me HOW to format the cell with brakcets around the hours. Cant use the tip without knowing how to format other than the format window, whick does not let me do that specific thing.
adding time
Rati Khetan
Need to know mor eabout how to add the tim eif in the format mm.ss.ms
Custom formats
Darren
kaylie wrote she does not understand how to add the brackets to the format. In format cells go to custom and type in [HH]:MM:SS in the edit box.
Thanks for the tip.
24th hour as previous day
cenya
I have an application that gives me dates and times as year, julian day, and hh:mm. I have made a concatenation formula to convert to mm/dd/yy hh:mm. It works well, but hour 24:00 comes out as 0:00 in the cell, so in a graph, the line returns to the beginning of the day, then jumps back to the 01:00 of the next day. This makes a strange looking and incorrect graph.
can anyone help me correct this problem?...convert the 24:00 to the last entry for the previous day.
thanks,
cenya
