date formatting

[Rip1971]

I have a wierd thing in my reports
In a generated report which i have no influence on there is a date variable that is done in different ways. however excel does not see it as a date.
By selecting cell and then F2 and leaving the cell again without saving the format does change.All cells are formated in short date.

what can i do to have all dates in same format so they can be used in a pivot for further usage.

20201130 Compliance report.xlsx
	O
49	13-MAR-2018
50	11/29/2019
51	9/17/2018
52	13-MAR-2018
Data

 

[Sufiyan97]

When formatted your data into Short date it gives me below result as you want

Book2
	A
1	3/13/2018
2	11/29/2019
3	9/17/2018
4	3/13/2018
Sheet1

 

[Rip1971]

When formatted your data into Short date it gives me below result as you want

Book2
	A
1	3/13/2018
2	11/29/2019
3	9/17/2018
4	3/13/2018
Sheet1

that is the wierd part. when i do it it doesn't change. only if i select cell in the bar not the cell itself and click on the checkmark.

 

[Sufiyan97]

Try
Select entire column where date is located and then
go to > Data > Text to columns > Finish

 

[Joe4]

that is the wierd part. when i do it it doesn't change. only if i select cell in the bar not the cell itself and click on the checkmark.

That means all the entries were entered as Text, not Date.

Try doing a "Text to Columns" on that column, selecting the Date option set to "MDY", and then click Finish.
That will convert them all to dates, and then you can apply any date format you want to them.

 

[Rip1971]

Try
Select entire column where date is located and then
go to > Data > Text to columns > Finish

That means all the entries were entered as Text, not Date.

Try doing a "Text to Columns" on that column, selecting the Date option set to "MDY", and then click Finish.
That will convert them all to dates, and then you can apply any date format you want to them.

This does do the trick. tried many things but this never crossed my mind.

Thank you both for this solution

 

[Joe4]

You are welcome.
Glad we were able to help.

 

[Sufiyan97]

You're Welcome...
Glad you get the solution..