how do i change date format in excel

May 27, 2010 at 01:17:16
Specs: Windows XP

I already have dates in as 20.10.09 but I want to change them to 20/10/09 ive tried changing them with the formula but not working, I have about 500 dates to change manually if needed but wanted to know how to do automatically

See More: how do i change date format in excel

Report •


#1
May 27, 2010 at 04:54:50

First, this should have been posted in the office forum. That said, here goes:

1- Highlight the cells (or rows) you want to change.
2- go to format>cells, then I believe its the contents tab and then numbers, the I believe you need special and date. On the right pane you choose the style you want, and OK.

I have used Excel fairly extensively, but currently I am using Openoffice.org at home instead of MS Office so at this moment I could not double check the exact step by step for you. If you have used long enough to use formulas, you should be able to find it with that description (sorry if I'm off on any detail).

You have to be a little bit crazy to keep you from going insane.
If all else fails, read instructions.


Report •

#2
May 27, 2010 at 10:36:55

If your date is configured as DAY/MONTH/YEAR then try this:

With your date of 20.10.09 in cell A1

Put this formula is cell B1

=DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))

It will probably displays the number 40106, this is how Excel stores dates,
simply Format the cell as a Date:
On the Task Bar,
Format
Cells
Dates
Select the format you like.

You now need to replace the formula that obtained the date, with a real date.

Select cell B1
On the Task Bar,
Edit
Copy
Edit
Past Special
Select Values
Click OK

MIKE

http://www.skeptic.com/


Report •

#3
May 27, 2010 at 10:56:51

Excel doesn't recognize the "." as a Date separator.
(This is by design.)

Are the dates all in one column?
(I have done this in Cell A1.)

If so, try this:

(I will assume 20.10.09 = 20 October 2009)

In an empty column, type the following formula:
(I have done this in Cell C1.)
=DATE(20 & RIGHT(A1,2), MID(A1,4,2), LEFT(A1,2))

That will convert it to a real Date.
(20 October 2009.)

If you use:
=DATE(RIGHT(A1,2), MID(A1,4,2), LEFT(A1,2))
You will get 20 October 1909.

Simply drag this formula down the list, to the end of your dates.

Select this Date List.
Right-Click the List.
Choose "Copy".

Select your "." separated List.
Right-Click.
Choose "Paste Special".
Select "Values", under "Paste".
(The other settings should be "None", under "Operation".)
(You can leave "Skip Blanks" and "Transpose" un-ticked.)

This will replace your Old List with an Actual Date List.

Format the List as:
Custom: dd/mm/yy


Report •

Related Solutions

#4
May 27, 2010 at 11:40:01

Have you guys consider how your formulas will perform for "single digit" months and/or days?

Your hardcoded "2" in the MID and LEFT functions isn't going to work very well for 1.1.2009 or 11.1.2009 or 1.11.2009 - assuming that is a format that the OP has to deal with.

This formula should handle both single and double digit dates:

=DATE(20&RIGHT(A1,2),
MID(A1,FIND(".",A1,1)+1,(FIND(".",A1)+1-FIND(".",A1)+1)),
LEFT(A1,LEN(A1)-FIND(".",A1)-1))


Report •

#5
May 27, 2010 at 12:06:59

This will convert any date formatted as DAY(dot)MONTH(dot)YEAR

=DATEVALUE(LEFT(RIGHT(A1,LEN(A1)-FIND(".",A1,1)),FIND(".",RIGHT(A1,LEN(A1)-FIND(".",A1,1)),1)-1)&"/"&LEFT(A1,FIND(".",A1,1)-1)&"/"&RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,".","#",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))))

MIKE

http://www.skeptic.com/


Report •


Ask Question