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