kungfauxn00b
New Member
- Joined
- Jul 25, 2017
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
Hey, I regularly download a report which includes date formats in the following format: "mm/dd/yyyy hh:mm AM/PM" but as I'm based in the UK, Excel converts the dates it recognizes to "dd/mm/yyyy hh:mm". For example, here's an example with two dates; 11th and 19th September:
As you can see, Excel has converted the 11th September to 9th November. Is there a way to convert all text formats and date formats to the same format?
What I have so far:
Which does this:
As you can see, as Excel has already converted 11th Sep to 9th Nov, it's just changing the format and not the actual date.
Any ideas greatly appreciated!!

As you can see, Excel has converted the 11th September to 9th November. Is there a way to convert all text formats and date formats to the same format?
What I have so far:
Code:
Sub ConvertDatesInSelection()
With Selection
.TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, FieldInfo:=Array(0, xlYMDFormat)
.NumberFormat = "mm/dd/yyyy hh:mm"
End With
End Sub
Which does this:

As you can see, as Excel has already converted 11th Sep to 9th Nov, it's just changing the format and not the actual date.
Any ideas greatly appreciated!!