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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,847
Office Version
  1. 365
Platform
  1. Windows
Put this formula in cell B2 and copy down for all rows:
Excel Formula:
=DATE("20" & MID(A2,2,2),MID(A2,5,2)+1,0)
 

DJFANDANGO

Board Regular
Joined
Mar 31, 2016
Messages
68
Office Version
  1. 2019
Platform
  1. Windows
Hi Joe,

Great, that works brilliantly, I guess I didn't think that I'd have a problem applying this to my existing data, please see the actual A2 example

If the cell A2 has instead ABC-AUDT-18/08-476

What numbers do I change to suit the date?

Put this formula in cell B2 and copy down for all rows:
Excel Formula:
=DATE("20" & MID(A2,2,2),MID(A2,5,2)+1,0)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,847
Office Version
  1. 365
Platform
  1. Windows
I am going to assume that your prefix may be of different lengths, but the ending number of characters after the date is always the same.
If that is true, this should work:
Excel Formula:
=DATE("20" & LEFT(RIGHT(A2,9),2),LEFT(RIGHT(A2,6),2)+1,0)

I guess I didn't think that I'd have a problem applying this to my existing data, please see the actual A2 example
That is a good example why it is usually not a good idea to oversimplify the question for our sake (not unless you are really confident that you can adapt it for your needs). ;)
It is usually best to explain the problem, exactly as it exists.
 

DJFANDANGO

Board Regular
Joined
Mar 31, 2016
Messages
68
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi Joe,

Great, that works brilliantly, I guess I didn't think that I'd have a problem applying this to my existing data, please see the actual A2 example

If the cell A2 has instead ABC-AUDT-18/08-476

What numbers do I change to suit the date?
It's Cool... I worked it out, thank you so so much!
 

DJFANDANGO

Board Regular
Joined
Mar 31, 2016
Messages
68
Office Version
  1. 2019
Platform
  1. Windows
Hi
I am going to assume that your prefix may be of different lengths, but the ending number of characters after the date is always the same.
If that is true, this should work:
Excel Formula:
=DATE("20" & LEFT(RIGHT(A2,9),2),LEFT(RIGHT(A2,6),2)+1,0)


That is a good example why it is usually not a good idea to oversimplify the question for our sake (not unless you are really confident that you can adapt it for your needs). ;)
It is usually best to explain the problem, exactly as it exists.
Hi Joe,

Yes, schoolboy error ;), the Prefix yes, can be different lengths as can the ending which can go into 4 digits... will this still work?
 

kvsrinivasamurthy

Well-known Member
Joined
Nov 6, 2013
Messages
647

ADVERTISEMENT

Since Some data in column will be treatedas string and some will be treated as date excel functions can not work.
Here is the UDF code that works
Code for UDF CompileDate

VBA Code:
Function CompileDate(Ip As String)

Dim Z
Dim k1 As Integer, k2 As Integer, k3 As Integer

Ip = Replace(Ip, "-", "/")
Z = Split(Ip, "/")
k1 = Val(Z(0))
k2 = Val(Z(1))
k3 = Val(Z(2))
CompileDate = DateSerial(k3, k1, k2)

End Function

How to use UDF.

Open the VB window
Insert a module.
Paste the above code.
close the VB window.

In C3 Enter the UDF. Function is available in list.

=CompileDate(B3)

Then copy down.
Any clarification welcome.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,847
Office Version
  1. 365
Platform
  1. Windows
Yes, schoolboy error ;), the Prefix yes, can be different lengths as can the ending which can go into 4 digits... will this still work?
Sorry I missed this post.

If you are still looking for a non-VBA solution, please post examples with the different length prefixes and suffixes, so I have a clear idea of all the different variations you are working with.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,847
Office Version
  1. 365
Platform
  1. Windows
Try this:
Excel Formula:
=DATE("20" & MID(A1,FIND("/",A1)-2,2),MID(A1,FIND("/",A1)+1,2)+1,0)
It should work as long as there is only one "/" in the entry.
 

kvsrinivasamurthy

Well-known Member
Joined
Nov 6, 2013
Messages
647
In C3 then copy down.
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")
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,114,135
Messages
5,546,136
Members
410,732
Latest member
tkouti1
Top