'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Sum Hours and Minutes Correctly in Excel
Question details
The user needs to accurately sum time durations in Excel, specifically addressing the issue where formulas return a 00:00 total because time is calculated or stored as text rather than a numeric duration.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Calculating total hours and minutes from a dataset containing time records or minute-based durations.
- Observed behavior
- Excel displays a sum of 00:00 because the formula attempts to aggregate time values that are formatted or stored as text instead of recognizable numeric durations.
Verify that your time data does not contain invisible spaces or text strings. Select your data cells and clear any text-specific formatting before applying time calculation formulas.
Convert Text Minutes to Time Values and Apply Custom Formatting
Convert your total minutes into a valid Excel time value by dividing by 1440, then use custom formatting to ensure totals over 24 hours display correctly.
Excel calculates time as a fraction of a 24-hour day. When time values are generated by formulas that output text, Excel cannot sum them, causing the total to show as 00:00. To fix this, you must convert the total minutes into a numeric value by dividing by 1440 (the total number of minutes in a day) and format the result appropriately.
Select the cell where you want your total to appear. Enter the formula =[@[Total Minutes]]/1440 or directly reference the cell containing your total minutes (for example, =A2/1440).
Right-click the cell containing the new formula and select 'Format Cells' from the context menu, or simply press the Ctrl + 1 shortcut on your keyboard.
In the Format Cells dialog, navigate to the Number tab and select 'Custom' from the Category list. In the Type box, enter [hh]:mm and click OK. The square brackets ensure hours over 24 are displayed as a total accumulated duration instead of rolling over to a new day.
Calculate Time Quickly and Accurately with WPS Spreadsheet
WPS Spreadsheet provides a seamless experience for handling complex time calculations and custom formatting. It fully supports standard Excel formulas, allowing you to calculate total durations without workflow interruptions.
- 1. Open your time dataset: Launch WPS Spreadsheet and open the file containing your hours and minutes data.
- 2. Convert total minutes to time: Select an empty cell and enter the formula =A2/1440 (assuming A2 contains total minutes) to convert the integer into a time fraction.
- 3. Apply duration formatting: Press Ctrl+1 to open the Format Cells dialog, choose the Custom category, type [hh]:mm into the input field, and click OK to correctly display the final duration.
Frequently Asked Questions
Why does my Excel time sum reset after 24 hours?
By default, Excel uses the standard time of day format (hh:mm). When the total exceeds 24 hours, it rolls over to a new day. To fix this, change the cell's custom format to [hh]:mm so Excel knows to display the total accumulated duration instead of the clock time.
How do I sum time values if they are currently stored as text?
If your time is stored as text, the SUM function ignores it and returns 00:00. You need to convert the text to numbers first using the TIMEVALUE function (like =TIMEVALUE(A2)) or by multiplying the text cell by 1, then format the resulting cell as a Time.
Why do I need to divide by 1440 to get a time value?
Excel calculates time based on days, treating 1 day as the integer 1. Because a day has 24 hours and each hour has 60 minutes, there are exactly 1,440 minutes in a day. Dividing a minute value by 1440 converts it into the precise daily fraction required by Excel to recognize it as time.
