My date format does not apply to every cell

[user_28CB]

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 

[#2]

@user_28CB 

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

[#3 · Accepted solution]

@user_28CB 

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