SUM function for time issue

[flyby99]

Dear users,

I have encountered an issue and I can't seem to get a fix for it. English isn't my native tongue and maybe I can't seem to find the correct keywords to find a solution, however here it goes.
Basically I've made a Call Centre statistics excel where we store infomation about answered calls/missed, time when logged on, total time spent talking and so on. I have an application where I export the data I need (in to seperate excel file) and I just copy/paste (as values) that information in to my excel sheet and nearly everything works except one thing.

So the issue is a simple SUM function for time.

For example in the month November, I have pasted the total time "idle, working time, work status, post processing" from the other excel which was exported . However the SUM function on the G34,H34,I34,J34,K34 does not work. The time when pasted will be shown as "04:03:50", however if I remove the 0 in front and make it "4:03:50" the sum function will work. Is there a way to force excel to sum the time while value of the cell stays "04:03:50" or make a quick workaround to remove the 0's.

I have attached the excel file in question.
Please, tell me if I have to clarify, because it might not be easy to understand what I'm trying to say.

[Pepe Le Mokko]

Hi
your time is not real time but text looking like time.
One way to make it real time is to select the column then click Data - Text to columns - Finish - ( one column at a time)
Eventually custom format the total as [h]:mm:ss

[Pepe Le Mokko]

Another way is to enter 1 in a cell somewhere - Copy - Select the ranges - Paste Special - Multiply -

This one will upset most of your formatting, though

[samba_ravi]

=SUM(IF(ISNUMBER(G3:G32+0),G3:G32+0))

Please Login or Register  to view this content.

Try this array (Shift+Ctrl+Enter) formula and copy towards right

[flyby99]

The data-text to columns option works. Could make a macro to do it for me, for every month.

Unfortunately the formula nflsales didn't work. A formula of course would be the best way to tackle this issue in my honest opinion.
Thank you guys.

[samba_ravi]

It is working for me, see the attached file

[flyby99]

nflsales, sorry, my bad. I'm a complete newbie in excel and was not using ctrl+shift+enter correctly. Works now!

++ for both of you. Thank you!