MrExcel Consulting
Your One Stop for Excel Tips & Solutions

Time Values in Excess of 24 Hours

January 1999 Challenge: You have created a time sheet in Excel. Workers enter their time in a range of cells formatted as hh:mm. When you use the SUM() function to total the hours, it only totals to 16 hours. Change the formula in cell B7 or offer a better approach to the problem. This month’s challenge is easier and may offer multiple approaches. Picking the best will be subjective. Decisions of the judges will be final.


Honorable mention to the several readers wrote in with a solution to format cell B7 as a number and to use the formula of =SUM(B2:B6)*24. However, Peter Rudenberg is this month’s Guru. He was the first to send in the best answer: Use a custom format of [h]:mm which causes the formula to work as expected. Congratulations, Peter! Douglas Hills from Australia was the 2nd reader to submit this answer, and the first from the Southern Hemisphere. Douglas – you win the right to be the guru of the month for Australia.