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

frazira

=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.).

frazira

Kelly Moore

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

frazira

I seem to get the same error, #UNPARSEABLE

Paul Newcome

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)

frazira

@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.

frazira

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?

frazira

Paul Newcome

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)




frazira

@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?

Paul Newcome

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)

frazira

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

Paul Newcome

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

frazira

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?