Comparing a Date

This formula is returning INVALID OPERATION. I am going to be doing much more than this as a final formula, but if I can't get this to work, I can't go any further. I'm not too familiar with Date formulas yet. Originally I had 07/01/2021, but it was suggested to be year, month, day?

=IF([Anticipated Project Start Date]@row = <21 / 7 / 1, "Yes")

Can anyone see what I am doing wrong here?

My Anticipated Project Start Date is a Date field and is restricted to Dates.

One interesting fact - I created a test column and called it "Date". Then I put this formula in it: =[Anticipated Project Start Date]@row. Both Columns are Date fields, restricted to Date values. And I get the following Error Message: "This column is restricted to dates." If I made the column called "Date" equal another column that has a date value, why wouldn't this work? Unless you can't put a formula within a date field?

Any help / direction is greatly appreciated.




Best Answers

  • Jill Cote
    Jill Cote ✭✭
    Answer ✓

    I was able to get some help from the Pro Desk, and it is now functioning as intended. For reference for anyone reading this. The successful formula is:

    =IF(AND([Anticipated Project Start Date]@row <= DATE(2021, 9, 30), [Anticipated Project End Date]@row >= DATE(2021, 7, 1)), [Project FTE Total]@row, "0.00")

    Yay!

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Jill Cote

    Thanks for posting your solution! I'm glad you were able to figure it out.

    For anyone else interested, when searching for a specific Date within a formula you'll need to use the DATE function (see here) to spell out the year/month/day.

    Cheers,

    Genevieve

Answers

  • Jill Cote
    Jill Cote ✭✭
    Answer ✓

    I was able to get some help from the Pro Desk, and it is now functioning as intended. For reference for anyone reading this. The successful formula is:

    =IF(AND([Anticipated Project Start Date]@row <= DATE(2021, 9, 30), [Anticipated Project End Date]@row >= DATE(2021, 7, 1)), [Project FTE Total]@row, "0.00")

    Yay!

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Jill Cote

    Thanks for posting your solution! I'm glad you were able to figure it out.

    For anyone else interested, when searching for a specific Date within a formula you'll need to use the DATE function (see here) to spell out the year/month/day.

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!