Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Convert 20050118 to a working date field
I have exported this field using RIGHT, MID, LEFT, & functions but I am left
with a column that looks like 2005/01/18. I then paste values and when I format as a date it does nothing until I press F2 for each cell. I have many rows, is there an easier way to make the date field usable? |
#2
|
|||
|
|||
One way that might work, select all dates and do edit replace and replace /
with / (yes repleace the forward slash with a forward slash) another thing might be to copy an empty cell, select the dates and do editpaste special and select add, then reformat as dates again yyyy/mm/dd -- Regards, Peo Sjoblom "Jessica" wrote in message ... I have exported this field using RIGHT, MID, LEFT, & functions but I am left with a column that looks like 2005/01/18. I then paste values and when I format as a date it does nothing until I press F2 for each cell. I have many rows, is there an easier way to make the date field usable? |
#3
|
|||
|
|||
=TEXT(A1,"0000\-00\-00")+0
Format as date. HTH Jason Atlanta, GA -----Original Message----- I have exported this field using RIGHT, MID, LEFT, & functions but I am left with a column that looks like 2005/01/18. I then paste values and when I format as a date it does nothing until I press F2 for each cell. I have many rows, is there an easier way to make the date field usable? . |
#4
|
|||
|
|||
Select dates and do Data / Text To Columns, then in the wizard choose the
appropriate date option for your column -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Jessica" wrote in message ... I have exported this field using RIGHT, MID, LEFT, & functions but I am left with a column that looks like 2005/01/18. I then paste values and when I format as a date it does nothing until I press F2 for each cell. I have many rows, is there an easier way to make the date field usable? |
#5
|
|||
|
|||
Thanks all! 2/3 answers worked!
"Jessica" wrote: I have exported this field using RIGHT, MID, LEFT, & functions but I am left with a column that looks like 2005/01/18. I then paste values and when I format as a date it does nothing until I press F2 for each cell. I have many rows, is there an easier way to make the date field usable? |
#6
|
|||
|
|||
Jason Morin wrote...
=TEXT(A1,"0000\-00\-00")+0 Did you test this? This would only work if the OP's cells contain 8-digit numbers formatted as "0000\/00\/00". However, if the OP were generating these cell values with LEFT, MID and RIGHT, then they're text, in which case your formula fubars. TEXT will treat A1 evaluating to "2005/02/23" as a value, but as a *DATE* value. That is, TEXT("2005/02/23","0") would return 38406 (1900 date system), so with A1 evaluating to "2005/02/23" your formula would evaluate to the rather unhelpful 0003-84-06. |
#7
|
|||
|
|||
Ken Wright wrote...
Select dates and do Data / Text To Columns, then in the wizard choose the appropriate date option for your column .... Picky - Data Text to Columns, select Fixed width then immediately click Finish would also work. |
#8
|
|||
|
|||
Cheers Harlan - Never considered trying it without explicitly specifying
dates. Got me curious though, so I went back and tried it with a different date format, ie YDM, eg 2005/18/01 and it wouldn't work, so I guess it's OK as long as the date is formatted the way the machine expects to see it, but otherwise you need to be explicit in telling it what format it's in. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Harlan Grove" wrote in message ups.com... Ken Wright wrote... Select dates and do Data / Text To Columns, then in the wizard choose the appropriate date option for your column ... Picky - Data Text to Columns, select Fixed width then immediately click Finish would also work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validating a date field | Excel Discussion (Misc queries) | |||
Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions | |||
How do I add a date field in a spreadsheet? | Excel Worksheet Functions | |||
convert julian date to gregorian date | Excel Discussion (Misc queries) | |||
How do I convert a Julian date into a regular date? | Excel Discussion (Misc queries) |