Formula to return a value if date is between entered date and calculated date

cg80 ✭✭✭
edited 11/20/23 in Formulas and Functions

I have 2 sheets. On the first sheet I have a start date (entered value). From the start date I calculate the start date of the quarter of the entered date, and I also calculate the end date of the quarter 1 year later. All three were created as Date fields. As an example, if the date entered in 11/16/2023, the quarter start date is calculated to 10/01/2023 and the year later date is 09/30/2024. All these formulas calculate correctly. 

I’m trying to build a formula on another sheet to check if a date is between the entered date and the year-later date. If the date is between, the result should be 1, not between then 2, and no date the result should be blank. I keep getting #Unparseable. 

=IF(MtgDate@row <>"",IF(AND(MtgDate@row >= {GrantStart},MtgDate@row <= {Yr1End})1,2),"")

GrantStart is a reference to the entered date on the first sheet. Yr1End is a reference to the calculated year later date. Any ideas of what needs to be changed?


Best Answer

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭
    Answer ✓

    If it is the formula you have here it is missing a comma from the nested IF.

    • =IF(MtgDate@row <>"",IF(AND(MtgDate@row >= {GrantStart},MtgDate@row <= {Yr1End}),1,2),"")


    Zach Hall

    Training Delivery Manager / Charter Communications


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!