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