Date Comparison Unexpected result

Options
dasberry
dasberry
edited 06/17/24 in Formulas and Functions

Option 1: =IF(([Last_Updated_Date]@row - $[Last_Updated_Date]$1) < 0, "Up to date", "Please Update Before Proceeding")

Option 2: =IF([Last_Updated_Date]@row < $[Last_Updated_Date]$1, "Up to date", "Please Update Before Proceeding")

I am writing the result in a column name Template_Updated which is Text/Number Column data type in the same sheet where dates are listed.

Last_Updated_Date column is a Date data type:

$[Last_Updated_Date]$1 = 05/22/24

[Last_Updated_Date]@row = 05/03/24

Output should be FALSE and produce "Please Update Before Proceeding", However, I get the TRUE value "Up to date".

If I invert the operator, I get correct results.

I also checked values of output with the following formulas:

=$[Last_Updated_Date]$1 - [Last_Updated_Date]@row returned 19

=[Last_Updated_Date]@row - $[Last_Updated_Date]$1 returned -19

Clearly, I missing something on calculating difference between dates here and would appreciate any input that could help alleviate this issue.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to provide a screenshot for context?

    You mentioned in your original post that "the output should be false", but you didn't indicate on which formula (or both) you were getting the wrong output on.

  • dasberry
    Options

    Sure, both formulas, option 1 and 2 were producing the same results. I have also realized the mistake upon trying to write it out again The screenshot below is based on the original question (hence why reversing the operator gave the expected result):

    =IF([Last_Updated_Date]@row < $[Last_Updated_Date]$1, "Please Update Before Proceeding", "Up to date")

    This is how it should have been written. My mental gymnastics were clearly in need of a break to process.

    Thank you for your response.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!