Problem adding time format.

[Luke Foreman]

Hi, I am a bit of a novice when it comes to Excel so there is probably quite a simple answer to my question.

I work for a telecoms provider and a customer has provided their billing data in an Excel spread sheet. I am trying to add the time of each call but every time I try to highlight the data and autosum or type in the formula it goes to the top of the list and puts SUM=() in the top box. I cant work out why it wont let me add the data. The work sheet is not protected or formatted by the customer and the amount of time being added does not come to more than 24 hours. I have tried retyping some of the data and then adding it and that works but I do not want to retype the whole spread sheet as there is several thousand data entries in it.

Please can someone help.

 

[Jubjab]

Welcome to the board!

My guess would be that the formatting of the cells is such that Excel does not recognize it as time. How did the data you retyped differ from the original data? That might give an indication on how to solve your problem.

 

[Luke Foreman]

The format of the cells is Text. When I type the data in it is displayed as general how ever I have tried copying the original data it to a new page and changing the format to general and it still wont add up.

 

[Jubjab]

Try this trick.
* Type in an empty cell somewhere the number 1.
* Copy that cell (ctrl-C)
* Paint all cells with the times (click on first cell, shift-ctrl-down arrow)
* Right-click on the painted area, select paste special->multiply

That should force a change to numbers. Finally change the cells' format (ctrl-1) to date/time.

 

[Luke Foreman]

Success!

Thanks very much, thats made my life a lot easier.