Cells don't update. F2 fixes one by one

[srburk]

This is the second time I've experienced this. The first time was on a personal spreadsheet that I created to track my electric bill. Excel 2003.

This time, I'm at work (Excel 2000).

I have 10,000 lines of date in column A through L.

The data was dumped from an application at work.

The data dump had dates in the following format HH:MMAM or HH:MMPM. I used =left(A1,4) to obtain the hour and minutes; =right(A1,2) to obtain the AM or PM; and then I concatenated the two back together to include a space in between the two. (example - Went from 01:25PM to 01:25 PM).

I formated the time column into a time format (after concatenation and copy/paste value to remove concatenation. This changed it from TEXT to TIME.

PROBLEM - It still looks the same. I decided to format the entire column to a different time format display (switched from 1:30 PM to 13:30:00). NOTHING HAPPENED; however, when I hit F2 in each field, it corrects its display.

This is not limited to the TIME format. Whereever there is a formula. If I change the values that the formula uses, nothing updates until I hit F2 in the actual formula field.

Any ideas???

 

[Norie]

Why not use the TIME or TIMEVALUE worksheet function to create true time values?

 

[NBVC]

What happens when you press F9?

If this re-calculates, then maybe you have calculations set on manual.

Go to Tools|Options and Calculation tab and select Automatic in the top area.

 

[Andrew Poulsom]

Have you got Calculation set to Manual (Tools|Options|Calculation tab)? If so you need to press F9 for your formulas to update after a copy/paste. The Status Bar will show Calculate when you need to.

 

[srburk]

What happens when you press F9?

If this re-calculates, then maybe you have calculations set on manual.

Go to Tools|Options and Calculation tab and select Automatic in the top area.

It is already set to automatic

 

[Andrew Poulsom]

The reason is that concatenation returns a string. Changing the format of the cell from Text to Time won't change what's in the cell - it's still a string. But pressing F2 Enter after changing the format is equivalent to re-entering what's there and it's converted to a number because of the format.

To get a ral time with concatenation try eg:

=(LEFT(A1,5)&" "&RIGHT(A1,2))+0

 

[srburk]

=(LEFT(A1,5)&" "&RIGHT(A1,2))+0

That did it. Thank you!