CODEWITHSUNDEEP
stringexceldate
Tillebeck
Tillebeck

Reputation: 3523

Excel conversion of US date (text) to european date (date)

I am pasting data from a US webpage into an excel sheet. A European Excel cannot understand American dates.... so I paste it as Text.

The difference between US and European dates is that the day and month is in a different order. How do I get from a "US text date" to real date that "European" Excel can understand? Reading it as dates doesn't work, so YEAR(), MONTH(), DAY() will not work on the native textstring. Some string conversion must be done first.

The third column is just to to show the problem. That is the result af MONTH function called on text in column "A". It is taking the day for the month and therefore throws an error when reaching the 13th of September.

TEXT (from US web)       EU date (real date)            MONTH(A1)
9/8/10 10:03 PM          8. Sep 2010                    8
9/9/10 10:03 PM          9. Sep 2010                    9
9/10/10 10:03 PM         10. Sep 2010                   10
9/11/10 10:03 PM         11. Sep 2010                   11
9/12/10 9:40 PM          12. Sep 2010                   12
9/13/10 9:40 PM          13. Sep 2010                   ERROR
9/14/10 9:40 PM          14. Sep 2010                   ERROR
9/15/10 9:28 PM          15. Sep 2010                   ERROR

If there is no "conversion function" then a solution could be to split m/d/yy to three columns and create a date from that. If so I need some help with splitting the text into a day, month and year.

UPDATE with answer.... Lazarus came up with one that works. First, split the text in column A into Year, month and day. Then combine them eg. using the DATE function

Upvotes: 1

Views: 20240

Answers (4)

Peter L
Peter L

Reputation: 1

You need to use text to columns to split the initial USA value into three components. Then you should get Day, month, year out of the work done. With these three components present re-assemble utilising Concatenate and use the Rept() command to add the leading zeroes back in where required.

Upvotes: -1

user15275684
user15275684

Reputation: 1

=((MID(A1,(SEARCH("/",A1,1)+1),((SEARCH(RIGHT(A1,4),A1,1)-1)-(SEARCH("/",A1,1)+1))))&"/"&LEFT(A1,(SEARCH("/",A1,1)-1))&"/"&RIGHT(A1,4))+0

Upvotes: 0

Lazarus
Lazarus

Reputation: 43064

Assuming your first date is in Cell A2, then the following will convert from the US to the European structure.

=DATE("2000"+MID(A2,FIND("/",A2,FIND("/",A2,1)+1)+1,
 FIND(" ",A2,1)-FIND("/",A2,FIND("/",A2,1)+1)-1),
 MID(A2,1,FIND("/",A2,1)-1),MID(A2,FIND("/",A2,1)+1, 
 FIND("/",A2,FIND("/",A2,1)+1)-FIND("/",A2,1)-1))

Upvotes: 4

Mark Baker
Mark Baker

Reputation: 212412

You could try using Excel's DATEVALUE() function to convert the text string to an actual Excel date value

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

Then you can number-format-mask this as you need

Upvotes: 1

Related Questions