Time not functioning correctly

**moonyboy99** · 07-16-2008, 09:57 AM

Hi Guys,

Just wondering if someone could help me with an annoying issue that i'm having at the moment.

Basically, I do a lot of extracting data from a system, which comes of in "excel" format. A lot of the data is time taken to do a job, which comes off such as 14:50 etc, i then want to add these figures together via auto sum. But excel just doesnt pick up the data at all, even when i change the format to [h]:mm.

But the interesting bit is, if i double click to cell as in to edit it, then straight away press enter, it then becomes a normal time cell and can be added, can i make the whole sheet do this automatically? Or i do i have to go through 5000 records clicking and pressing enter?

Please help!!

Many Thanks,

WIll

**NBVC** · 07-16-2008, 10:08 AM

Try entering a 1 in any cell and then copy it.

Select the entire range you want to fix and go to Edit|Paste Special.

Select Multiply.

Click Ok.

You may have to do some reformatting of the times...

**daddylonglegs** · 07-16-2008, 10:20 AM

You might also be able to sum with this formula

=SUMPRODUCT(A1:A5000+0)

**moonyboy99** · 07-17-2008, 02:37 AM

Hi Guys, thanks for the suggestion, the first one does work, after i re format time setting, thanks a lot. Saves me hours of time!

I can't get the 2nd method to work though, it just seems to come up with a number i can't for the life of me where it came from!

Thanks,

Will