Formula to convert date format from mm/dd/yyyy to dd/mm/yyyy

crook_101

Well-known Member

Joined

Oct 20, 2008

Messages

682

• Dec 8, 2010

• #2

Hi,

Try the following in B1

Code:

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

Then copy & paste as values and format as English date

HTH

Ian

 

Last edited: Dec 8, 2010

Upvote 0

suprsnipes

Active Member

Joined

Apr 26, 2009

Messages

434

• Dec 8, 2010

• #3

Thanks for the reply.

I tried the formula (I've tried a few others also), in this case it returns #VALUE!. I'm not too sure as to why. Just to clarify this is the formula I used.

Code:

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

The contents of cell A1 shown in date format is as follows;
12/09/2010

however, when shown in general format it is;
40433

Hope I can fix this

 

Upvote 0

A

alansidman

Well-known Member

Joined

Feb 26, 2007

Messages

8,102

Office Version

1. 365

Platform

1. Windows

• Dec 8, 2010

• #4

Your formula is slightly different from what Ian proposed.

In the center of your forumula you have Left(A1A9..... that should probably be Left(A1......

Double check this and try again.
Alan

 

Upvote 0

suprsnipes

Active Member

Joined

Apr 26, 2009

Messages

434

• Dec 8, 2010

• #5

Sorry that was a typo ... just tried it again to make sure. I'm having the same problem. The below was the formula I attempted to use.

Code:

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

suprsnipes said:

Thanks for the reply.

I tried the formula (I've tried a few others also), in this case it returns #VALUE!. I'm not too sure as to why. Just to clarify this is the formula I used.

Code:

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

The contents of cell A1 shown in date format is as follows;
12/09/2010

however, when shown in general format it is;
40433

Hope I can fix this

Click to expand...

 

Upvote 0

Ruddles

Well-known Member

Joined

Aug 24, 2010

Messages

5,845

Office Version

1. 365

Platform

1. Windows

• Dec 9, 2010

• #6

Date serial 40433 is the 12th of September and you say it's displaying as 12/09/2010. That's dd/mm/yyyy format.

Isn't that what you want?

 

Upvote 0

G

GlennUK

Well-known Member

Joined

Jul 8, 2002

Messages

11,871

Office Version

1. 2019
2. 2013

Platform

1. Windows

• Dec 9, 2010

• #7

If it's already a date serial it's been converted from whatever it was entered as, incorrectly. Where did your data come from exactly?

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Dec 9, 2010

• #8

Use Data|Text To Columns specifying Date MDY at Step 3.

 

Upvote 0

JamesW

Well-known Member

Joined

Oct 30, 2009

Messages

1,197

• Dec 9, 2010

• #9

Can you not just format the cell as custom: dd/mm/yyyy?

 

Upvote 0

Ruddles

Well-known Member

Joined

Aug 24, 2010

Messages

5,845

Office Version

1. 365

Platform

1. Windows

• Dec 9, 2010

• #10

That was the point I was trying to make: the cell contains 40433 which is the date serial for the 12th of September and the OP states it's displaying as 12/09/2010, so it must already be in dd/mm/yyyy format.

Unless I'm missing something blindingly obvious (which isn't beyond the bounds of impossibility).

 

Upvote 0