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]

Put this formula in cell B2 and copy down for all rows:

=DATE("20" & MID(A2,2,2),MID(A2,5,2)+1,0)

 

[DJFANDANGO]

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:

=DATE("20" & MID(A2,2,2),MID(A2,5,2)+1,0)

 

[Joe4]

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:

=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]

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]

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:

=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]

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

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]

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]

Try this:

=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]

In C3 then copy down.
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")