Date sorting issue

[DJFANDANGO]

Recently our systems were updated, we used to have DD/MM/YYYY now they have are MM/DD/YYYY format, now my data is horrendously messed up...

I've tried Data>Text to Columns>Delimiter etc... but it's not able to convert all the dates, the old data has been converted, but I'm left with 'dates' and 'text' in my date ranges....?

My computer is set to DD/MM/YYYY

Below you can see how the data is represented on my system, on the 'Reference Id' its displayed as 'YY/MM-number of...' I'm not sure if tis can be used as a 'reference' to fix the date issue with a macro/vba?

Thanks in advance.

GASafetyObservation637371481765547378.xls
	A	B
1	Reference Id	Observation Date
2	-18/08-476	8/31/2018
3	-18/08-477	8/31/2018
4	-19/02-403	2/22/2019
5	-18/07-068	7/30/2018
6	-18/12-914	12/09/2018
7	-18/12-934	12/11/2018
8	-19/01-049	1/13/2019
9	-19/01-147	1/21/2019
Sheet1

 

[Joe4]

A column reference not required. B column data can be changed todd/mm/yyyy format

=TEXT(IF(ISTEXT( B2),DATE(RIGHT(B2,4),LEFT(B2,FIND("/",B2)-1),MID(B2,FIND("/",B2)+1,2)),DATE(YEAR(B2),DAY(B2),MONTH(B2))),"dd/mm/yyyy")

It should be pointed out that will return a Text value, not a Date one. You just need to be aware of that in case you want to perform sorting or calculations on it.

 

[kvsrinivasamurthy]

It should be pointed out that will return a Text value, not a Date one. You just need to be aware of that in case you want to perform sorting or calculations on it.

I have tested that it will be in date format in the cell. If the problem is there you can use this formula and Format the cell for date as dd/mm/yyyy.

=IF(ISTEXT( B2),DATE(RIGHT(B2,4),LEFT(B2,FIND("/",B2)-1),MID(B2,FIND("/",B2)+1,2)),DATE(YEAR(B2),DAY(B2),MONTH(B2)))

 

[Joe4]

I have tested that it will be in date format in the cell. If the problem is there you can use this formula and Format the cell for date as dd/mm/yyyy.

Yes, by removing the TEXT function that you had on there originally, it can now return a Date value.
The TEXT function will always return a Text value.