Time Format of cell does not update until I double click on cell.

scott0728

New Member
Joined
Dec 3, 2008
Messages
3
I have a spreadsheet that is exported from AccuCall Web in Excel format. I need to sum the time of all long distance phone calls (which are all in a single column). When I use the Sum function and specify the range of cells with time, it shows 0:00:00 (I have formatted the cell containing the sum as [h]mm:ss). If I change the formatting of the individual cells in the column to 1:30:55 PM format, the cell still displays 0:01:22 for a one minute and 22 second call, and the sum does not change. However, if I double click on any of those formatted cells in the range and then simply hit escape to make no change, the sum properly adds the 0:01:22 (or whatever) to the total.

Since the column contains hundreds of cells, I am looking for a way to not have to double click on each cell in order for the formatting of that cell to update. Suggestions?

Scott
 
Last edited by a moderator:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Have you got calculate turned OFF

Tools > Options > Calculation "Automatic" ticked
 
Upvote 0
Welcome to the Board!

Set the column to time and then select all the cells in that column. Go to Data-->Text To Columns and press finish. That should get them all in one swoop.

Hope that helps.
 
Upvote 0
schielrn, that worked! Can't believe I wasted an hour on something you showed me how to fix in 3 seconds. Thanks!

Scott
 
Upvote 0
Automatic should be on normally, just noticed, is the lack of a colon a typo here [h]mm:ss i.e does it actually look like [h]:mm:ss which would be right
 
Upvote 0
schielrn, that worked! Can't believe I wasted an hour on something you showed me how to fix in 3 seconds. Thanks!

Scott
I assume this data is either being pasted in from another program or imported in from somewhere else. This is a typical problem. Glad I could be of assistance, don't worry, you will learn from whatever you do in Excel and there are many things that I have worked on for days only to find there is something built into Excel already that took far less time.
 
Upvote 0
Sorry, to bring up an old thread...
I have the same problem, but in need the information to stay as data. If i do text to columns i cannot refresh data.
 
Upvote 0
This is an issue I've had on several occasions, can't believe I just found this post 6 years after it was written. But I'm glad I did, it just saved me a lot of time, thanks!

Welcome to the Board!

Set the column to time and then select all the cells in that column. Go to Data-->Text To Columns and press finish. That should get them all in one swoop.

Hope that helps.
 
Upvote 0
Sorry, to bring up an old thread...
I have the same problem, but in need the information to stay as data. If i do text to columns i cannot refresh data.

Unfortunately I think if it is a refreshed data set, then you should be able to just format it using Control+1, but when you refresh the data it will probably go back to the "bad" format and you would ahev to re-format again. I have had that issue before as well.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top