Excel - Sum of Times is Wrong - How to Format and Sum Time Values - Episode 1468
1,854 views · Published 10 November 2011 · 2:59 · Indexed 14 November 2026
Channel: MrExcel.com · 2011 · Education
Microsoft Excel Tutorial: Calculate Total Time and Payroll in Excel Welcome to the MrExcel podcast. In this episode, we will be discussing how to add time in Excel. Our listener, Sean, sent in a question about a spreadsheet he was working on where he needed to calculate the total hours worked by a group of employees. Despite using the correct formula, the times were not calculating correctly. Let's dive in and see how we can solve this issue. Sean's spreadsheet had hundreds of rows, but the problem could be summed up in one line. He had a group of employees who worked an eight-hour shift from 7am to 3pm, making $9.25 per hour. The task was to calculate the total pay for 21 employees. However, when Sean tried to multiply the number of employees by the number of hours, the result was not what he expected. This is a common issue that many Excel users face, but luckily, there is a simple solution. Upon examining Sean's spreadsheet, I noticed that he had formatted the time cells using the "time" option under the number tab. However, this was not the correct format for calculating total hours. To get the correct result, we need to use the "37:30:55" format, which is specifically designed for time values that exceed 24 hours. Once we applied this format, the calculation gave us the correct answer of 168 hours. But, this format also displays the number of seconds worked, which is not necessary in this case. So, we can customize the format by removing the seconds and adding an @ sign to treat the value as numeric. Now, we have the correct total hours worked, but we still need to calculate the total pay. This is where another Excel quirk comes into play. In order to convert the time value to hours, we need to multiply it by 24. This is because Excel stores time as one twenty-fourth of a day. So, to get the total pay, we need to multiply the hourly rate by the total hours worked. By using these tricks, we can get the correct result and make Sean's spreadsheet work seamlessly. I hope this episode has helped you understand how to add time in Excel and solve any issues you may have encountered. Thank you for tuning in to the MrExcel podcast and a special thanks to Sean for sending in his question. Don't forget to subscribe to our channel for more helpful Excel tips and tricks. See you next time for another netcast from MrExcel. Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Times do not total correctly in Excel (00:18) Calculation of Total Hours (00:28) Calculation of Total Pay (00:41) Confusion with Time Format (01:00) Format Cells for Correct Answer (01:10) Choosing Correct Time Format (01:23) Customizing Time Format and Removing Seconds (01:33) Explanation of Custom Format (01:47) Multiplying for Total Pay (02:02) Testing Calculation (02:12) Conversion to Actual Hours (02:29) Clicking Like really helps the algorithm This video answers these common search terms: how to sum a column of time in excel how to sum in excel for time time formatting and calculations in excel how to sum time duration in excel how to sum time hours in excel how to sum time into hours in excel excel time calculation tutorial how to sum times in excel schedule how to sum hours minutes time in excel how to sum in excel with time how do i sum time in excel ? how to add sum of time in excel how do you sum time inexcel how to do a time sum in excel how to total time duration in excel how to total time excel how to make times total in excel how to calculate time totals in excel how to use excel formula total time summing time in excel excel formula for time addition adding time values in excel totaling time durations in excel time-based calculations in excel Shaun has a beautiful spreadsheet to calculate staffing levels and expense.He needs to multiply shift length by the number of staff and the answer that he is getting is . . . immaterial to the task at hand. With a few changes in the Formula and Cell Formats, Bill shows us in Episode #1468 how to get the real resulting Total Hours and Cost. MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today! Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153146/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920