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

Options
✭✭✭
edited 11/20/23

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?

Tags:

• ✭✭✭✭✭✭
Options

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

Best,

Zach Hall

Training Delivery Manager / Charter Communications

• ✭✭✭✭✭✭
Options

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

Best,

Zach Hall

Training Delivery Manager / Charter Communications

• ✭✭✭
Options

I can't believe I missed it! Many thanks for your quick response

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!