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 “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
- 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.