| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula with DateValue and doing Fill doesn't work, need help
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula with DateValue and doing Fill doesn't work, need help
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. "Peo Sjoblom" wrote: Toolsoptionscalculation, set it to automatic -- Regards, Peo Sjoblom "Theresa W" <Theresa wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula with DateValue and doing Fill doesn't work, need help
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. "Peo Sjoblom" wrote: Toolsoptionscalculation, set it to automatic -- Regards, Peo Sjoblom "Theresa W" <Theresa wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula with DateValue and doing Fill doesn't work, need help
Toolsoptionscalculation, set it to automatic
-- Regards, Peo Sjoblom "Theresa W" <Theresa wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| HOW DO I ENTER THIS FORMULA TO CONVERT TEXT TO DATE =DATEVALUE(, | Excel Worksheet Functions | |||
| Cell fill colors don't appear to work... | Excel Discussion (Misc queries) | |||
| I have a list of data, fill in the gaps. FILL function won't work | Excel Discussion (Misc queries) | |||
| fill color doesn't work | Excel Worksheet Functions | |||
| Auto-fill won't work | Excel Worksheet Functions | |||
Hybrid Mode
