Date format

[jjuan]

i have a date format like this - 13.02.2007(dd.mm.yyyy)
i like to convert it to date so that i can sort it by date.
i already used the format cells to change the category type to date but
nothings happen.
i also replace the dot(.) to slash(/) but the excel read it as mm.dd.yyyy

can someone give me a tips?
thanks!

 

[SteveW]

Terminology !

Dates are stored as numbers by Excel in a cell

Format allows you to alter the way Excel displays them.

If it was originally ente4red as 13.02.2007 then that is how it will be
stored ie as TEXT

Now when you convert the Dots to / with say Replace or manual edit
Excel will then treat the new text as input and as it is now in
Date ready format it will accept it.

If your original text entered data was dd.mm.yyyy, excel doesn't know that
and as it has US style Date enabled (mm/dd/yyyy) it treats it as such.


If you alter your regional settings to UK (dd/mm/yyyy) format before
changing the dots to slashes it should work
otherwise you will have to reconstruct the date from the
components of the the original

ie a1 = "13.02.2007"
b1 = date(right(a1,4),mid(a1,4,2),left(a1,2))

Steve

 

[Guest]

Data > Text to Columns > Next > Next > Under Column data format select Date,
in the drop down select DMY