Date change/format

[jacques1]

Hi All. Ist tru that the way the date are shown can be changed by the date format tool, provided the date was entered the correct way. I think we are missing each other. I think the way the date was entered (17.08.07) and converted to (17/08/07) was good. At first it was in text so one wouldnt have known whether it will be taken as yymmdd, ddmmyy, mmddyy etc. well it end up like as 17(yy), 08(mm), 07(dd) instead as 17(dd), 08(mm) and 07(yy). My Q now is is there a way (formula) to swap these orders around...?

 

[Andrew Poulsom]

Have you tried Data|Text to Columns, specifying the date order at Step 3?

 

[venkat1926]

sorry ignores

 

[venkat1926]

I have not understood your problem clarly.
let me see
you enter in a cell e.g.A1
8/17/07 repeat 8/17/07
Do you want this to look like
17,08,07
if this presumption is correct
highlight the cell (A1)
format-number-custom
in the small window type
dd,mm,yy
and click ok.

you can even try in the custom
dd,mmm,yy

If this is not what you want apologies
venkat

 

[RichardU]

Is there a way to format a date no matter how it is entered? i.e. work out how it was entered then fit it into the format wanted?

 

[Stormseed]

Is there a way to format a date no matter how it is entered? i.e. work out how it was entered then fit it into the format wanted?

Could you show us the way a date is entered and you want it formatted as a date?

 

[RichardU]

Well i have a spreadsheet used by many people and they enter it differently

i.e.
ddmmyyyy
dd/mm/yyyy
d/mm/yyyy
dd/m/yyyy
d/m/yyyy
d/m/yy

etc you get the point.

I wanted it to convert the input into the format dd.mm.yyyy no matter how it is entered?

 

[Stormseed]

well, I dont know if dates entered as "ddmmyyyy" will work.

However, for other entries you can select the entire column and format it as Custom Date as walked thru by Mr. Venkat in his above post. It works perfectly.

 

[RichardU]

No they dont!!!

Anyone know how i can make it work?

 

[Stormseed]

what happened? what problem you are experiencing? I have tested this on my computer...it works smoothly.

Care to post some details?