Nested IF, using MONTH
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", "?")
Best Answer
-
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))), "?")
Answers
-
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))), "?")
-
Hi Paul,
That formula is closer, it's no longer #UNPARSABLE, but it is returning "?" every time.
-
Is your End Date column set as a date type column? If so, exactly how is it being populated?
-
That was the issue! Thank you!!
-
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.
-
@Paul Newcome You, sir, have saved my sanity. Thank you!
-
@Kelly Meck Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!