1

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.

  • of the top of my head.. / is an escaped character indicator.. so to search for one forward slash you have to write // or maybe even four slashes: //// – Danny '365CSI' Engelman May 8 '15 at 17:56
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.