Formula with DateValue and doing Fill doesn't work, need help

[Theresa W]

I have over 6,000 rows with a DATE in TEXT format in column B. I have a
formula

=IF(ISERROR(DATEVALUE(B5)),B5,DATEVALUE(B5))

When I copy one cell at a time or do a range fill it is putting the same
date in all cells instead of the date in the cell reference. I have tried
doing this with both relative and absolute reference and neither one works.
Interesting though is if I hit F2 and Enter it will update the date correctly.

Example of what the copy and paste did
3-Apr-06 4/3/2006 Forumla=IF(ISERROR(DATEVALUE(B5)),B5,DATEVALUE(B5))
3-NOV-03 4/3/2006 Forumla=IF(ISERROR(DATEVALUE(B6)),B6,DATEVALUE(B6))
30-Apr-06 4/3/2006
18-Mar-07 4/3/2006
13-Dec-03 4/3/2006
12-Jun-03 4/3/2006
13-Nov-04 4/3/2006
31-Jul-07 4/3/2006
21-Apr-04 4/3/2006

 

[Peo Sjoblom]

Tools>options>calculation, set it to automatic

 

[Theresa W]

WOW...thank you. I had turned off calculations last week because of a
spreadsheet re-calculating every time an edit and I wanted to do that one
when I was done. I didn't realize it affected the date formula I was doing.
Thank you so much.