Date sorting issue

DJFANDANGO

Board Regular
Joined
Mar 31, 2016
Messages
68
Office Version
  1. 2019
Platform
  1. Windows
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
AB
1Reference IdObservation Date
2-18/08-4768/31/2018
3-18/08-4778/31/2018
4-19/02-4032/22/2019
5-18/07-0687/30/2018
6-18/12-91412/09/2018
7-18/12-93412/11/2018
8-19/01-0491/13/2019
9-19/01-1471/21/2019
Sheet1
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,848
Office Version
  1. 365
Platform
  1. Windows
A column reference not required. B column data can be changed todd/mm/yyyy format

VBA Code:
=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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

kvsrinivasamurthy

Well-known Member
Joined
Nov 6, 2013
Messages
648
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,848
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,139
Messages
5,546,179
Members
410,731
Latest member
keobongmacao
Top