I was recently asked to help solve a problem. Below is a screenshot from an Excel file generated from an Internet Log File. The file contains a list of the websites that a particular individual had visited on 1st February during working hours. (for the purpose of this example I have made up the data). The individual’s Manager was not pleased with the amount of time that this person had spent using the Internet for non-work-related activities.
The Manager used the following formula in I1: =SUM(F2:F13). As you can see, the formula results in 0.
However, entering the formula =F2+F3+F4+F5+F6+F7+F8+F9+F10+F11+F12+F13 into I2 and formatting the cell as Time (hh:mm:ss) displays the correct result.
The Problem
The “times” in column F are not really times. Because they have come from elsewhere and imported into Excel, they look like times but they are actually text values. This often happens to numeric, date and time data when you export from another system into Excel.
To prove this, I selected F2 and changed the format from General to Number.
The way Excel works with times is the value 1 represents a 24 hour period, so for example if you had 12:00:00 in a cell and you changed the format to Number, it will display as 0.50 because 12:00:00 is 50% through a 24 hr period.
When I changed the format of the cell, nothing happened.
The other give-away was that when I selected F2:F13 and looked at the very bottom of the screen, it said COUNT:12, indicating that there were 12 cells selected, whereas if the cells had contained numeric values (i.e. real time values), it would have said SUM: 1:21:05
So why does the formula in I2 generate the correct result?
This is where Excel is sometimes too clever for it’s own good. If you use a normal formula (i.e. =F2+F3+F4 etc, even though the cells contain “text”, Excel recognises that the “text” entries look like times and therefore treats them as time values.
However, if you use =SUM it treats them as text values which evaluate to 0 – hence the answer as 0
The Solution
- Select the cells in column F containing “time values”
- Click the Data button on The Ribbon
- Select Text to Columns
- Leave it set to Delimited and click Next
- Remove any ticks from the Delimiters boxes and click Next
- Leave it set to General and leave destination set to F2
- Click Finish
The General setting tells Excel to convert the data in the cells to whatever data type it thinks it should use – in this case it is intelligent and uses time. The SUM function in I1 should now result in the correct answer.
This fixed my problem!!! thanks so much!!!
Thank you
Thank you, It helps and it is an easy way to solve it.
I followed the steps, and it didn’t work for me.
@Janmesh – can you send me the file you used please. Drop me a not via the contact form on this website and I’ll write back with my email address
What if the time data is in rows instead of columns.(
Unfortunately there’s no quick way but it can be done…
Select the cells with the “times” in
Click Copy
Select a blank cell with lots of blank cells below it
Click the arrow underneath Paste and select Transpose
Use Text to Columns as per my original tutorial
Select the cells with the times in
Click Copy
Select the first cell of the original cells
Paste > Transpose – so you are pasting back over the original data
Thank you…..you saved me a lot of time
This can be done a much quicker way.
Do a custom formula using the following [hh]:mm
Times will count past 24 hours for the total amount.
Thank you, It worked for my after trying different sites.
Thanks, Worked wonders unlike other suggestions on other sites
This is EXACTLY what I needed. I was trying to track my time spent at work each day. I could add the time duration for two days if it did not exceed 24 hours. If the time duration was => 24, Excel would display 0:00 which was not correct. Using this custom [h]:mm formatting solved the issue.
Thank you! I was looking everywhere for this solution.
Thank you SO SO SO much!!
Thanks so much! I watched several other YouTube tutorials but none worked until I found yours!
Awesome! It worked.
As an alternative, you can use a custom type of: [h]:mm or [h]:mm:ss. The brackets around the [h] is important. Its not in the list naively, you’ll need to type it into the Type box and select Ok.
Right click cells, select Format Cells. Go to Number tab. Select Custom in the Category menu. Type [h]:mm in the Type box. Presto – it’ll sum the hours for you correctly henceforth.
I’ve tried everyone’s suggestions here but I keep getting “0:00” after summing the cells. It’s driving me nuts
After trying many other things that other sites had suggested, this is what finally worked for me. Thanks!
What a blessing. This is perfect explanation thank you so much.
I found yet another solution! I was still getting 0 after doing this and the mentioned fixes in the above comments. The issue ended up being the formula I used when calculating the duration between start/end times (as shown in column F in this example). I was using formula =TEXT(X#-X#, “[hh]:mm:ss”). I actually contacted support and they had me use the standard =(X#-X#) instead.
The SUM formula doesn’t recognize TEXT formatted cells in its calculations. When you use =TEXT, it recognized numerical values as text when referencing for a formula. By just using =(X#-X#), it calculates the times as numerical values as it should. I got the incorrect formula from another website. It worked as it should until I wanted to add up the sum of all times, then my sum always lead to 0.
thanks a lot
I tried this again but it didn’t work 🙁
WOW, mind blown! Thank you
Thanks so much, problem solved
thank you thank you. I couldn’t find any other site addressing this nuanced problem!
This is a god send!!! so simple and very well explained. THANK YOU!!!!!!
You are very welcome