Invalid formula error using IF AND

Hello @Himesh

It's possible that the error is occurring because you are specifying day 0 in your DATE function.

Try:

=IFERROR(IF(AND($[Month Helper Column]$1 >= [Start Date]@row, $[Month Helper Column]$1 <= [Finish Date]@row), [80%]@row / [Duration (Months)]@row, IF($[Month Helper Column]$1 = DATE(YEAR([Finish Date]@row), MONTH([Finish Date]@row) + 3, 1), [20%]@row, "")), "N/A")

https://community.smartsheet.com/discussion/comment/438993#Comment_438993

Hiya, thanks, but still the same error unfortunately. Is there anything else that could be causing it?

Try splitting it into 2 IF statements to test each condition. If you are getting your 80/20 results correctly, then it has to do with how the function is set up.

If you don't, then it's possible that the column properties are preventing you from comparing those values.

It could be beneficial to use the net days function instead of the actual dates within the conditions.

https://community.smartsheet.com/discussion/comment/439007#Comment_439007

It's definitely the last part of the if statement that's causing the problem. I've changed it a little bit and added another helper column [Finish Date Month End] so it's an exact match to the month helper column, but I'm getting an: #UNPARSEABLE error now. Here's the formula: =IF($[Month Helper Column]$7 = MONTH([Finish Date Month End]@row) + 3), [20%]@row, "").

Any idea what I'm doing wrong here? The last part of the formula should basically do this: If the Month Helper column is three months greater than the Finish Date Month End column then show the 20% value and if not then leave it blank. I think the error is stemming from the +3, but I'm not sure how else I'm supposed to add it.

Looks like you are trying to compare a number to a date.

Maybe this will work,

=IF(MONTH($[Month Helper Column]$7) = MONTH([Finish Date Month End]@row) + 3), [20%]@row, "")

https://community.smartsheet.com/discussion/comment/439182#Comment_439182

Still doesn't work, but it's fine. I've managed to find a roundabout way of doing it by adding three months to the Finish Date Month End column ("=IFERROR(DATE(YEAR([Finish Date]@row), MONTH([Finish Date]@row) + 3, 1), DATE(YEAR([Finish Date]@row) + 1, 1, 1)) - 1")) and then matching it on the month columns using "IF($[Month Helper Column]$7 = [Finish Date Month +3]@row, [20%]@row, ""))"

This works in conjunction with the 80% breakdown over the duration formula, but there's another problem I've encountered with a Month Helper column formula: "=IF(MONTH([Month Helper Column]7) = 12, DATE(YEAR([Month Helper Column]7) + 1, 1, 1) - 1, DATE(YEAR([Month Helper Column]7), MONTH([Month Helper Column]7) + 2, 1) - 1)"

What I need is for each cell to populate the end of the next month, but it breaks down after month 11. Is a fix for this?

Thanks for the help.

Nice job.

Is it possible that it is due to the reference changing? Or is that something you intended? I only ask because it's what you started with.

=IF(MONTH([Month Helper Column]$7) = 12, DATE(YEAR([Month Helper Column]$7) + 1, 1, 1) - 1, DATE(YEAR([Month Helper Column]$7), MONTH([Month Helper Column]$7) + 2, 1) - 1)

Also, this says if the month is equal to 12. You could try changing it to greater than 12, then add 1 to the year. Then it will hopefully include month 12.

MONTH([Month Helper Column]7) > 12, DATE(YEAR([Month Helper Column]7) + 1, 1, 1)

https://community.smartsheet.com/discussion/comment/439237#Comment_439237

Hi,

So, I'm getting a #UNPARSEABLE error with this formula: =MONTH([Month Helper Column]7) > 12, DATE(YEAR([Month Helper Column]7) + 1, 1, 1).

With regards to the first big formula - the $ reference was needed for the Month Helper Column since the helper month needed to be absolute, but the Finish Date Month +3 column did not. No problem with this one as it works with the modified formula.

I think everything will work smoothly as long as I can get the Month Helper Column to populate the next month per cell (so getting a version of the above formula to work).

Thanks for all the help!

Well as little as it was, no problem!

What is the column property for that helper column?

I have sheets that analyze rolling time frames and the function for each date is different. In this case, I can't use a column function and using additional helpers to tell the function what to add was excess work and unnecessary.

Writing in the function was the easiest, fastest, and most robust way I could set it up. BUT, these sheets are only set up to analyze 18 months worth of data, no more and no less (which is the biggest downfall).

I start with =MONTH()

Next row is =MONTH()+1, then MONTH()+2, and so on.

It is beneficial to have at least two sheets when analyzing data. 1 with the source data and 1 with the consolidated data/summaries.