Date sorting issue

DJFANDANGO

Board Regular
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?

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

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

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

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

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

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

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:
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
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:

Replies
6
Views
60
Replies
5
Views
58
Replies
3
Views
96
Replies
6
Views
64
Replies
6
Views
168