Re-format date (mm/dd/yyyy into dd/mm/yyyy)

[Robert]

Hi All,

Could someone help me with the following:
I have a range of dates which I want to convert from mm/dd/yyyy into
dd/mm/yyyy, my formula
(=MID(T1474,LEN(T1474)-6,2)&"/"&LEFT(T1474,2)&"/"&RIGHT(T1474,4))
works for most of the dates though in case of 1/12/2007 where the
month only has one position (instead of 01) my formula takes "1/" for
mm (instead of "01")

Can one of you advice how to capture this issue in my (or a different)
formula?

Many thanks!!!!

Rgds,
Robert

 

[Lori]

You could try: Data > Text to Columns > Next > Next > Date:MDY
on the column.

In the last step you can choose Destination in another adjacent column
if you want to put the results in another column.

 

[Ron Rosenfeld]

Hi All,

Could someone help me with the following:
I have a range of dates which I want to convert from mm/dd/yyyy into
dd/mm/yyyy, my formula
(=MID(T1474,LEN(T1474)-6,2)&"/"&LEFT(T1474,2)&"/"&RIGHT(T1474,4))
works for most of the dates though in case of 1/12/2007 where the
month only has one position (instead of 01) my formula takes "1/" for
mm (instead of "01")

Can one of you advice how to capture this issue in my (or a different)
formula?

Many thanks!!!!

Rgds,
Robert

It seems from your formula that you want the result to be a text string, and
not a formatted Excel Date (an Excel date is a serial number formatted to look
like a date).

If that is the case, then the formula to use depends on the nature of the
original in T1474.

If T1474 is a serial number formatted to look like a date, then use this:

=TEXT(T1474, "dd/mm/yyyy")

If, on the other hand, T1474 is a text string, and you want to convert it to a
text string, then this should work:

=TEXT(DATE(RIGHT(T1474,2),LEFT(T1474,FIND("/",T1474)-1),
MID(T1474,FIND("/",T1474)+1,FIND(CHAR(1),SUBSTITUTE(
T1474,"/",CHAR(1),2))-FIND("/",T1474)-1)),"dd/mm/yy")

Obviously, there are various permutations of whether T1474 is text or a date,
versus what you want your result to be.

For example, if T1474 was a date, and you wanted your result to be a date, then
simply:

=T1474 and format the result (Format/Cell/Number/Custom Type: "dd/mm/yy")


--ron

 

[KANNAN S]

G1 CONTAINS 10/8/2009
(DD/MM/YYYY)
[WITHOUT PRECEDING 0 FOR SINGLE DIGIT MONTH AND DATE)


L1 =TEXT(G1,"mm/dd/yyyy") RESULT (10/08/2009)
=RIGHT(L1,4) RESULT (2009) YEAR
=LEFT(L1,2) RESULT (2009) MONTH
=MID(L1,4,2) RESULT (2009) DATE
=DATE(M1,O1,N1) 8/10/2009

 

[KANNAN S]

A SIMPLE METHOD....


lET G1 CONTAINS 10/8/2009
(DD/MM/YYYY)
[WITHOUT PRECEDING 0 FOR SINGLE DIGIT MONTH AND DATE)


L1 =TEXT(G1,"mm/dd/yyyy") RESULT (10/08/2009)
=RIGHT(L1,4) RESULT (2009) YEAR
=LEFT(L1,2) RESULT (2009) MONTH
=MID(L1,4,2) RESULT (2009) DATE
=DATE(M1,O1,N1) 8/10/2009

KANNOOZE...!

Hi All,

Could someone help me with the following:
I have a range of dates which I want to convert from mm/dd/yyyy into
dd/mm/yyyy, my formula
(=MID(T1474,LEN(T1474)-6,2)&"/"&LEFT(T1474,2)&"/"&RIGHT(T1474,4))
works for most of the dates though in case of 1/12/2007 where the
month only has one position (instead of 01) my formula takes "1/" for
mm (instead of "01")

Can one of you advice how to capture this issue in my (or a different)
formula?

Many thanks!!!!

Rgds,
Robert

Data > Text to Columns > Next > Next > Date:MDY
on the column.

In the last step you can choose Destination in another adjacent column
if you want to put the results in another column.

On Saturday, November 20, 2010 11:31 PM KANNAN S wrote:
G1 CONTAINS 10/8/2009

(DD/MM/YYYY)

[WITHOUT PRECEDING 0 FOR SINGLE DIGIT MONTH AND DATE)





L1 =TEXT(G1,"mm/dd/yyyy") RESULT (10/08/2009)

=RIGHT(L1,4) RESULT (2009) YEAR

=LEFT(L1,2) RESULT (2009) MONTH

=MID(L1,4,2) RESULT (2009) DATE

=DATE(M1,O1,N1) 8/10/2009

 

[KANNAN S]

G1 CONTAINS 10/8/2009
(DD/MM/YYYY) [WITHOUT PRECEDING 0 FOR SINGLE DIGIT MONTH AND DATE)


L1 =TEXT(G1,"mm/dd/yyyy") RESULT (10/08/2009)
=RIGHT(L1,4) RESULT (2009) YEAR
=LEFT(L1,2) RESULT (2009) MONTH
=MID(L1,4,2) RESULT (2009) DATE
=DATE(M1,O1,N1) 8/10/2009

Hi All,

Could someone help me with the following:
I have a range of dates which I want to convert from mm/dd/yyyy into
dd/mm/yyyy, my formula
(=MID(T1474,LEN(T1474)-6,2)&"/"&LEFT(T1474,2)&"/"&RIGHT(T1474,4))
works for most of the dates though in case of 1/12/2007 where the
month only has one position (instead of 01) my formula takes "1/" for
mm (instead of "01")

Can one of you advice how to capture this issue in my (or a different)
formula?

Many thanks!!!!

Rgds,
Robert

Data > Text to Columns > Next > Next > Date:MDY
on the column.

In the last step you can choose Destination in another adjacent column
if you want to put the results in another column.

On Saturday, November 20, 2010 11:31 PM KANNAN S wrote:
G1 CONTAINS 10/8/2009

(DD/MM/YYYY)

[WITHOUT PRECEDING 0 FOR SINGLE DIGIT MONTH AND DATE)





L1 =TEXT(G1,"mm/dd/yyyy") RESULT (10/08/2009)

=RIGHT(L1,4) RESULT (2009) YEAR

=LEFT(L1,2) RESULT (2009) MONTH

=MID(L1,4,2) RESULT (2009) DATE

=DATE(M1,O1,N1) 8/10/2009

.





lET G1 CONTAINS 10/8/2009

(DD/MM/YYYY)

[WITHOUT PRECEDING 0 FOR SINGLE DIGIT MONTH AND DATE)





L1 =TEXT(G1,"mm/dd/yyyy") RESULT (10/08/2009)

=RIGHT(L1,4) RESULT (2009) YEAR

=LEFT(L1,2) RESULT (2009) MONTH

=MID(L1,4,2) RESULT (2009) DATE

=DATE(M1,O1,N1) 8/10/2009



KANNOOZE...!