Highlight dates and using ? change from 9/9/2015 to 09/09/2015
- Thread starter Eloise T
- Start date
NARAYANK991
Excel Ninja
Hi ,
If you have a lot of data like this , then you can go through the following steps. Remember , this is a problem because your date format is :
dd-mm-yyyy
or some variation of it , whereas the data that you think is a date is actually a text string ; Excel is unable to decipher it as valid dates because it is not in the above format.
To convert it to the above format , we first need to isolate such data from the set ; this can be done using the TYPE function , which returns 1 if the data is numeric ( all dates are numeric ) and 2 if the data is text.
Once such data has been isolated , you can process the text data using the formula :
=(MID(D9,3,2) & "-" & LEFT(D9,1) & "-" & RIGHT(D9,4))+0
which will convert the text value into a numeric value , which Excel can interpret as a valid date.
Thereafter it is just a matter of changing the cell format.
See the attached file.
Narayan
If you have a lot of data like this , then you can go through the following steps. Remember , this is a problem because your date format is :
dd-mm-yyyy
or some variation of it , whereas the data that you think is a date is actually a text string ; Excel is unable to decipher it as valid dates because it is not in the above format.
To convert it to the above format , we first need to isolate such data from the set ; this can be done using the TYPE function , which returns 1 if the data is numeric ( all dates are numeric ) and 2 if the data is text.
Once such data has been isolated , you can process the text data using the formula :
=(MID(D9,3,2) & "-" & LEFT(D9,1) & "-" & RIGHT(D9,4))+0
which will convert the text value into a numeric value , which Excel can interpret as a valid date.
Thereafter it is just a matter of changing the cell format.
See the attached file.
Narayan
Eloise T
Active Member
Thank you!!!!!!
p45cal
Well-Known Member
You can do this in one fell swoop with Text-to-columns (dates which are Excel dates are left untouched):
Select the dates (in a single column) that you want to convert, go to data|Text-to-columns, in step 1 choose Delimited, in step 2, untick all tick boxes, in step 3 choose Date and in the drop down next to that choose MDY (this tells Excel how the original data is laid out), then click Finish.
QED.
As an aside, this mixing of dates and text often occurs in Excel when you have copy/pasted a column of dates from elsewhere (usually text) say from a web site, this is when Excel's 'helpfulness' is a pain; it tries to help by converting what it thinks are dates into Excel dates, but how it does this depends on your Locale setting in Excel/Windows, so if you're in the US and it sees 3/4/2015 it converts it into March 4th 2015, had you been in the UK it translates to April 3rd 2015. Wherever you are, if it sees a value above 12 where it expects to see a month number, it leaves it as text.
The mere fact that it has failed to convert some dates implies that it is likely that some other dates will have also been incorrectly converted (the day/month will be transposed).
The way to stop Excel being helpful is to format the destination cells to Text before pasting anything in them, then you do a text-to-column for the whole column as above.
Select the dates (in a single column) that you want to convert, go to data|Text-to-columns, in step 1 choose Delimited, in step 2, untick all tick boxes, in step 3 choose Date and in the drop down next to that choose MDY (this tells Excel how the original data is laid out), then click Finish.
QED.
As an aside, this mixing of dates and text often occurs in Excel when you have copy/pasted a column of dates from elsewhere (usually text) say from a web site, this is when Excel's 'helpfulness' is a pain; it tries to help by converting what it thinks are dates into Excel dates, but how it does this depends on your Locale setting in Excel/Windows, so if you're in the US and it sees 3/4/2015 it converts it into March 4th 2015, had you been in the UK it translates to April 3rd 2015. Wherever you are, if it sees a value above 12 where it expects to see a month number, it leaves it as text.
The mere fact that it has failed to convert some dates implies that it is likely that some other dates will have also been incorrectly converted (the day/month will be transposed).
The way to stop Excel being helpful is to format the destination cells to Text before pasting anything in them, then you do a text-to-column for the whole column as above.
Eloise T
Active Member
Highlighting and clicking beats formulas any day! GREAT SOLUTION! Thanks!