# Need help with IF formula if a date is between beginning and end of the month

Options
edited 07/12/24

=IF([FIT - Start Savings Date]@row, <>TODAY(1012024), ANDTODAY(1312024), (\$[FIT - Estimated \$ Saved]@row / 12), 0))

Please help fix this formula. #UNPARSEABLE error. I'm trying to bring in 1/12th of annual savings once a project completes. Using the savings start date, bring back 1/12th annual savings if the start savings date is between 1st and end of each column/month (Jan2024, Feb2024, etc.).

Options

• ✭✭✭✭✭✭
Options

Hey @frazira

=IF(AND(ISDATE([FIT - Start Savings Date]@row),[FIT - Start Savings Date]@row>=DATE(2024,1,1), [FIT - Start Savings Date]@row⇐DATE(2024,1,31), [FIT - Estimated \$ Saved]@row / 12)

Will this work for you?
Kelly

• Options

I seem to get the same error, #UNPARSEABLE

• ✭✭✭✭✭✭
Options

It looks like you have an aarrow instead fo a less than or equal to:

=IF(AND(ISDATE([FIT - Start Savings Date]@row),[FIT - Start Savings Date]@row>=DATE(2024,1,1), [FIT - Start Savings Date]@row <= DATE(2024,1,31), [FIT - Estimated \$ Saved]@row / 12)

• Options

@Paul Newcome I think this is closer, but now I get #INCORRECT ARGUMENT. Could the fact that the Estimated \$ Saved is an Index/Match vs. just a text/number be an issue? For the FIT End Savings Date, it would not calculate and end date off the Start savings date until I converted that to an actual value vs. a lookup.

• Options

in excel, someone said to use =IF(\$F2>G\$1,0,IF((\$F2+365)<G\$1,0,\$E2/12)). It changes for each month, but I could build it. Does anyone know if this will work or how to make it work in smartsheets?

Options
• ✭✭✭✭✭✭
Options

Maybe a misplaced parenthesis that I hadn't noticed the first time around…

=IF(AND(ISDATE([FIT - Start Savings Date]@row),[FIT - Start Savings Date]@row>=DATE(2024,1,1), [FIT - Start Savings Date]@row <= DATE(2024,1,31)), [FIT - Estimated \$ Saved]@row / 12)

• Options

@Paul Newcome I really appreciate you working with me. That formula does work, however, I'm not sure how to tweak it so that it returns 1/12th of a number for 12 months after the "savings date". Any suggestions on that?

• ✭✭✭✭✭✭
Options

That would be the last part (in bold).

=IF(AND(ISDATE([FIT - Start Savings Date]@row),[FIT - Start Savings Date]@row>=DATE(2024,1,1), [FIT - Start Savings Date]@row <= DATE(2024,1,31)), [FIT - Estimated \$ Saved]@row / 12)

• Options

That puts 1/12th in just Jan 2024. I need it to show 1/12 in each month Jan-Dec, then stop.

• ✭✭✭✭✭✭
Options

You would need to put the formula (and adjust the dates) into each of the columns you want an output in.

• Options

If my Savings Date is 12/31/2024, then I would expect to see an amt in Dec/Jan and then Feb, Mar, etc. Even when I change the dates in each column, it still doesn't bring back 1/12. Date is >= to 2/1/2025 and ⇐ to 2/28/2025. So Dec 2024 is not between those dates, yet it's within the 12 months following my Savings Date, in this instance 12/31/2024. The next row has a Savings Date of 8/1/25. I have too many rows to update the dates in the formula for each row. In excel, we use =IF(\$E3>F\$2,0,IF((\$E3+365)<F\$2,0,\$D3/12)). Does smartsheets have something like this where it adds 365 days?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!