Nested IF, using MONTH

RGarrels

I feel like I am missing something simple- maybe someone else can see it?

I want the formula to produce a "1" if the date in the row is January (01), February (02) or March (03). I want it to return a "2" if the date in the row is in April (04), May (05) or June(06), and so forth, to identify in which quarter of the year the project is scheduled to be complete.

If there is no date, just return a "?".

What am I missing?

=IF([End Date]@row= MONTH(01),MONTH(02)MONTH(03) ,"1",IF([End date]@row=MONTH(04),MONTH(05),MONTH(06) , "2", IF([End Date@row=MONTH(07),MONTH(08),MONTH(09) , "3", IF([End Date]@row=MONTH(10),MONTH(11),MONTH(12), "4", "?")

Paul Newcome

Try this instead...

=IFERROR(IF(MONTH([End Date]@row)<= 3, 1, IF(MONTH([End Date]@row)<= 6, 2, IF(MONTH([End Date]@row)<= 9, 3, 4))), "?")

Paul Newcome

Try this instead...

=IFERROR(IF(MONTH([End Date]@row)<= 3, 1, IF(MONTH([End Date]@row)<= 6, 2, IF(MONTH([End Date]@row)<= 9, 3, 4))), "?")

RGarrels

Hi Paul,

That formula is closer, it's no longer #UNPARSABLE, but it is returning "?" every time.

Paul Newcome

Is your End Date column set as a date type column? If so, exactly how is it being populated?

RGarrels

That was the issue! Thank you!!

Paul Newcome

Happy to help. 👍️

For troubleshooting...

The MONTH function will throw an error if the referenced cell value is not a date. In this case we used it to output the ? when there is a blank/no date. Since it was outputting the ? for ALL rows, that led me to believe that the cells being referenced did not contain an actual date value. The typical reason for that would be the column type is not a date type column.

Kelly Meck

https://community.smartsheet.com/discussion/comment/343776#Comment_343776

@Paul Newcome You, sir, have saved my sanity. Thank you!

Paul Newcome

@Kelly Meck Happy to help. 👍️