Excel Change date formats using excel formulas

C

Charimore

Joined
Apr 23, 2013
Messages
5
Reaction score
0
Hiya everyone

At my workplace, we have a excel workbook (2010) that holds clients details, which is a mess at the moment and I've taken on the job of trying to sort it out. At the moment the excel workbooks have dates on it that is formatted as 05.06.13. I wondered if there was a formula that I can change these to 05/06/2013.

I would be much appreciated if you could help.

Thank you
 
You could just change the formatting of the cells. If you select the cells with the dates, right click on them and go to Format Cells. Go to the Custom Category. Then on the righty under "Type:", you'll want to type "mm/dd/yyyy" (if you're in the US, "dd/mm/yyyy" if you're elsewhere) without the quotes. That should reformat the cells. If you really want to do it with a formula, would use the TEXT formula. For example if you have the first date in B2, you would put the following formula in the cell that you would like to have the new format:
Code: 
=TEXT(B2,"mm/dd/yyyy")

Again, you can change the m's and d's depending on where you want the month and day. Let me know if that does it.
 
Hiya Alow

Thank you for getting back to me. I've tried that before but it didn't work. In Format Cells excel recognises 05.06.13 as General, but I want to changed it to 05/06/2013 (dd/mm/yyyy). However, I've tried the formula you suggest and it seem to work but I just realised that there are formats on the sheets that has got 5.5.13 and it doesn't work with those also where there are blanks in the cell the formula returns 01/00/1900.

Eg.

Date​
Formula returns …
04/12/2012​
12/04/2012

01/00/1900

01/00/1900

01/00/1900
08/01/2013​
01/08/2013
08/01/2013​
01/08/2013
08/01/2013​
01/08/2013

01/00/1900
08/01/2013​
01/08/2013
8.01.13
8.01.13
08/01/2013​
01/08/2013
15.01.13
15.01.13

01/00/1900

01/00/1900
04.11.12
04.11.12
29.1.13
29.1.13

01/00/1900


Is there any way to solve this?

Thank you

C
 
I'm having a hard time following your example with the way the formatting gets thrown around here. The reason you're getting 01/00/1900 for the blanks is because of the way Excel stores dates. In Excel dates are serial numbers starting with January 1st, 1900 and counting up 1 for every day past that, so a blank, which is a 0, gives you that. You can avoid that with an IF and and ISBLANK formula. For the values that are stored in a format of #.##.## or ##.##.## or ##.#.##, it gets a little more difficult because Excel is storing those as text. If it was ALWAYS #.#.##, I could create a pretty simple formula for you, but I doubt that is the case. Let me know if there is any more info you can give, especially related to the specifics of the data, maybe a column of the dates you are trying to convert, and I'll start thinking of a way to get a formula working for you.
 
I think I've got it. It looks like you're using DD/MM/YYYY, so I'll stick with that. Try the below:
Code: 
=IF(ISBLANK(B2),"",TEXT(DATEVALUE(SUBSTITUTE(B2,".","/")),"dd/mm/yyyy"))
Assuming B2 is the value you want to convert as a date. Let me know if this solves it for you.
 
Last edited:
Hiya Alow

Sorry about how the table is presented. There were a lot of spacing in the rows so I copy and pasted part of the table in word, adjusted the rows and copy and pasted table in the email. I don't know why it's come out like that but before posting it it was fine.

I've tried your formula and it worked on my laptop at home which uses Excel 2002/3, can't remember, but it doesn't seem to work here on Excel 2010 at work - it's saying "VALUE" for all the cells. Is it because I had to change the cell reference to A4 on the Excel 2010?

Please help.

Thank you

Charimore
 
Back
Top