Format Dates problem

[Itaybill]

Hi

I import text files into one excel file, but a certain column called "date" is quite problematic: the dates are in american format (MM/DD/YY) and i need to convert them to UK Format (DD/MM/YY). i managed to solve this by using a formula, but the problem is solved only some of the dates (example below - only for the ones that the day is larger than 12). also, i the excel the dates that the day is below 12 are written akwared (aligned to the side)

Date Corrected
02/22/11 22/02/11
02/23/11 23/02/11
02/24/11 24/02/11
02/28/11 28/02/11
03/01/2011 #Value!
03/02/2011 #Value!
03/03/2011 #Value!
03/08/2011 #Value!
03/09/2011 #Value!


the formula is:=DATEVALUE(MID(D2,4,2)&"/"&LEFT(D2,2)&"/"&RIGHT(D2,2))


Thanks!

Itay

 

[AlphaFrog]

Try something like this...

=IF(ISTEXT(D2), DATEVALUE(MID(D2,4,2)&"/"&LEFT(D2,2)&"/"&RIGHT(D2,2)), DATE(YEAR(D2),DAY(D2),MONTH(D2)))

 

[Itaybill]

it works! thanks!

by the way, do you have any idea why it happens? in the text file, all the dates seems identical...

 

[rs2k]

Another option (Which worked for me)

Sub DateChangeUKtoUS()
Dim i As Integer
x = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To x
        Range("C" & i).Value = Format(Range("A" & i).Value, "mm/dd/yyyy")
    Next i
End Sub

Sub DateChangeUStoUK()
Dim i As Integer
x = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To x
        Range("C" & i).Value = Format(Range("A" & i).Value, "dd/mm/yyyy")
    Next i
End Sub

Does US to UK or vice-versa,

Cheers
Colin

 

[AlphaFrog]

it works! thanks!

by the way, do you have any idea why it happens? in the text file, all the dates seems identical...

When the day is 12 or less in the imported date, Excel will try to use the day value as the month and interpret it as a date even though it is not the date you want. When the day is greater than 12,Excel cannot use the day as the month value because month values are not greater than 12. So that imported date remains as text. It is not interpreted as a date.

The new formula checks if the value is text. If it is, it parses the text into a date. If it is not text but rather just a misinterpreted date, it swaps the day and month.