Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question

migrated from stackoverflow.com Oct 22 '12 at 10:14

3 Answers

up vote 0 down vote

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.

share|improve this answer
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
up vote 0 down vote

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, of course, that you need to verify your data is stored as dates and not text first. Hope that helps!

-- jm

share|improve this answer
up vote 0 down vote

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.)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.