Forum Discussion
My date format does not apply to every cell
Hi. if you look at your A-column it's Aligned to the left and probably a text. You have to change it to Excels date-format before you change it back to text in the format you wish.
It looks as if the data is in American date format with the month first.
If your Computer uses some kind of logical based date-system the conversion might get messy.
2 solutions that might work
1.)
Select column A. goto
DATA->text to column->next, next: Date MDY (with the month first. Use the correct letters in your language)
If this doesn’t work (the AM/PM might mess things up) you could try.
2.)
Select a date-text-cell somewhere in column A
Data->Get an transform-> table/interval:
Since the source data is USA-formated the data is probably transformed correctly (close and load).
This gives you a table with excel-dates that can be manipulated in any way you want
The dates are in USA format m/d/yyyy (m/j/aaaa in French). 9/30/2020 is not recognized as a valid date by your system.
Try this macro:
Sub ConvertDates()
Dim rng As Range
Application.ScreenUpdating = False
For Each rng In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
rng.Offset(0, 1).Value = CDate(rng.Value)
Next rng
Application.ScreenUpdating = True
End Sub