Stacked IF Statement Using AND and OR

KayZee_2025

I'm trying to write a formula that evaluates three columns of dates and pulls in the value of a fourth column (MWh@row) if each of the first three column's dates have already occurred in the past or will happen before the end of this year, and I'm not having any success. I keep getting an "#INCORRECT ARGUMENT" error. Can anyone make any suggestions as to where I am going wrong?

=IF(AND(OR([Local Permits Acquired (F)]@row < TODAY(), [Local Permits Acquired (F)]@row < DATE(YEAR(TODAY()), 12, 31)), IF(OR([IX Phase 3 Studies (F)]@row < TODAY(), [IX Phase 3 Studies (F)]@row < DATE(YEAR(TODAY()), 12, 31)), IF(OR([HV Breaker PO Executed (F)]@row < TODAY(), [HV Breaker PO Executed (F)]@row < DATE(YEAR(TODAY()), 12, 31)), MWh@row))))

Thank you!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Give this a try:

=IF(YEAR(MAX([Local Permits Acquired (F)]@row, [IX Phase 3 Studies (F)]@row, [HV Breaker PO Executed (F)]@row)) <= YEAR(TODAY()), MWh@row)

First we find the latest date of the three using the MAX function. If the latest date falls within the specified range, then the other two must as well. If the latest date does not fall within the range, we don't meet your "and" requirement that all three must.

Once we get the latest date, we extract just the year using the YEAR function. This keeps us from having to specify a certain date since all you care about is that it has or will have happened by the end of this year.

Comparing that to the current year using less than or equal to YEAR(TODAY()) will let us know if the "max" date of the three will happen before then end of this year.

If it does, output MWh@row.

All comments

Carson Penticuff

If I understand correctly, you can simplify your formula significantly. If you are looking for any dates that are happening in the currently year or past years, you can evaluate that with one statement. Give this a try:

=IF(AND(YEAR([Local Permits Acquired (F)]@row) <= YEAR(TODAY()), YEAR([IX Phase 3 Studies (F)]@row) <= YEAR(TODAY()), YEAR([HV Breaker PO Executed (F)]@row) <= YEAR(TODAY())), MWh@row, "")

Paul Newcome

Give this a try:

=IF(YEAR(MAX([Local Permits Acquired (F)]@row, [IX Phase 3 Studies (F)]@row, [HV Breaker PO Executed (F)]@row)) <= YEAR(TODAY()), MWh@row)

Comparing that to the current year using less than or equal to YEAR(TODAY()) will let us know if the "max" date of the three will happen before then end of this year.

If it does, output MWh@row.

Adrien Leduc

Okay I've taken a look at your formula and about what I think you're trying to achieve, I see you put both AND and OR and 3 nested IF, but only one "result". Based on that I think you need only one IF and nest your OR in the AND which would be:

=IF(AND(OR([Local Permits Acquired (F)]@row < TODAY(), [Local Permits Acquired (F)]@row < DATE(YEAR(TODAY()), 12, 31)), OR([IX Phase 3 Studies (F)]@row < TODAY(), [IX Phase 3 Studies (F)]@row < DATE(YEAR(TODAY()), 12, 31)), OR([HV Breaker PO Executed (F)]@row < TODAY(), [HV Breaker PO Executed (F)]@row < DATE(YEAR(TODAY()), 12, 31))), MWh@row,"")

I am not 100% sure about the placement and number of parenthesis, let me know how it goes.

KayZee_2025

That did the trick - love the logic there. Thank you so much!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.