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

  • Thread starter Thread starter Robert
  • Start date Start date
R

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
 
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.
 
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
Click to expand...

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
 
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
 
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.
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...
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
Click to expand...
Click to expand...
.





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...!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top