How to convert dd/mm/yyyy into yyyy/mm/dd in excel 2003?

I am supposed to convert date format from `dd/mm/yyyy` or `dd/mm/yy` to `yyyy/mm/dd` in excel 2003, i think it is possible in excel 2007 by selecting `format-cell-->custom date-->yyyy/mm/dd`.

-

migrated from stackoverflow.comOct 22 '12 at 10:14

This question came from our site for professional and enthusiast programmers.

Assume we have a value like `mm/dd/yyyy` in cell `A4`.

Get text before the first `/` character (=> `mm`)

``````=LEFT(A2;SEARCH("/";A4)-1)
``````

Get text before the second `/` character (=> `dd`)

``````=MID(
A4;
SEARCH("/";LEFT(A4;SEARCH("/";A4)))+1;
SEARCH("/";RIGHT(A4;LEN(A4)-SEARCH("/";A4)))-1
)
``````

Get text after the second `/` character (=> `yyyy`)

``````=RIGHT(A3;SEARCH("/";LEFT(A3;LEN(A3)-SEARCH("/";A3)))-1)
``````

Rebuild date (=> `dd/mm/yyyy`)

``````=CONCATENATE(
MID(A4;
SEARCH("/";LEFT(A4;SEARCH("/";A4)))+1;
SEARCH("/";RIGHT(A4;LEN(A4)-SEARCH("/";A4)))-1)
& "/" &
LEFT(A4;
SEARCH("/";A4)-1)
& "/" &
RIGHT(A4;
SEARCH("/";LEFT(A4;LEN(A4)-SEARCH("/";A4)))-1)
)
``````
-
@Jawa I intentionally used > intsead of four spaces since the last formula is way to long :) –  nixda Oct 16 '13 at 17:33
I switched to code formatting (four spaces) as it was code, not a quote. Now the codes are formatted, so it's even easier to follow what's actually happening. –  Jawa Oct 16 '13 at 19:56

You could use a formula to change it. Assuming your date is in A1, put this in A2:

``````=TEXT(A1,"yyyy/mm/dd")
``````

Omnikrys is right, you need to verify your data is stored as dates and not text first.

-

Put the original date in one cell, and set it's "Date" format to the type "3/14/2001". Then set the format of a second cell to "Custom" yyyy/m/d. Set the second cell value to "=A1" or whatever the first cell is. Anything entered in the first cell will be converted.

(Or at least this works in 2007.)

-

It is the same in 2003 except the category isn't custom date it is just custom then fill in yyyy/mm/dd or whatever format you want to use.

If the dates are not changing format there is a good chance they are stored as text. To force Excel to treat them as not text do the following.

1. Put the number 1 in a blank cell
2. Click that cell and copy it
3. Select the dates in question (by column or whatever)
4. On the Edit menu select Paste Special...
5. Under operation select Multiply
6. Click OK

That will force excel to reevaluate the dates without actually changing their value.

-
i tried it in 2003 the option is present but it doesnt seem to work, –  user1557976 Oct 22 '12 at 5:46
Works fine for me... I actually reformat pretty much all my dates to yyyy-mm-dd so do it daily. Are you sure the value you are getting is a date and not a string? –  Omnikrys Oct 22 '12 at 5:50
well i have a sheet with dates dd/mm/yy now when i custom format it to yyyy/mm/dd in 2007 it converts the valu to yyyy/mm/dd , but when same procedure is applied in 2003 version ,the previous date format remains the same, i mean to say suppose i have 15/11/12 now when i apply the procedure on this cell , i should get value 2012/11/15 automatically. –  user1557976 Oct 22 '12 at 6:03
And what happens when you go to the date section and pick a different date format? If nothing then the value is not being read by Excel as a date. –  Omnikrys Oct 22 '12 at 6:05