Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

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

add comment

4 Answers 4

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
add comment

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.

share|improve this answer
add comment

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)
 )
share|improve this answer
    
@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
add comment

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
add comment

Your Answer

 
discard

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