Forum Discussion
My date format does not apply to every cell
Hello guys,
I retrieve a data file (excel) from a web platform. I want to modify the date&time format from
| 10/1/2020 7:57:00 AM |
to
| 10/01/2020 07:57 |
and show it in a new sheet.
I applied this formula to the raw data
The formula is working well until a certain date where the format just don't want to change anymore.
Does anyone had the same issue ? How can I fix it ?
Thanks for your help
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
2 Replies
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
