sorting by calculated date column in date format

Question

I have a calculated column of date strings. The calculated field reads 5 other columns and is filled with a date STRING from the other fields. The calculated field has an type of date. The problem is the string is in format d/M/YYYY meaning that the string could be 1/1/2000 or 10/12/2000. When I try and sort it, it sorts the column like text.

I need to sort it by the date, when I try and sort by this field it sorts it like a string.

I have tried to convert it to a date field with a calculated field. However since I don't know how many characters the string will be, I am having trouble converting.

=FIND("/",[column]) 

Is also not working for some reason.

I've also tried

=DATEDIF(DATE(YEAR(RIGHT([Date Received],4)),MONTH(MID([Date Received],FIND("/",[Date Received])+1,FIND("/",[Date Received],3)-FIND("/",[Date Received])-1)),DAY(LEFT([Date Received],FIND("/",[Date Received])+1))),DATE(YEAR(1980),MONTH(1),DAY(1)),"D")

but the converting date does not work because find returns #VALUE.

Answer 1

I copied/pasted your formula, multiple issues:

• DAY/MONTH/YEAR function are not required as they extract a day/month/year from a valid Date adn you are working with textstrings.
• DATEDIF function calculates from a past date to a more recent date, not the other way around (I did not know this either) So =datedif(date(1980,2,1),date(1980,1,1),"D") gives an error calculation.
• You had a -1 +1 mixup in the day calcalution

Working Formula:

=DATEDIF(
    DATE(1980,1,1)
   ,DATE(
        RIGHT([Date Received],4)
       ,MID([Date Received],FIND("/",[Date Received])+1,FIND("/",[Date Received],3)-FIND("/",[Date Received])-1)
       ,LEFT([Date Received],FIND("/",[Date Received])-1)
    )
,"D")