Formula issue comparing dates

First, this community is GREAT, and has really helped me with understanding formulas, so I'm hoping someone can help with a frustrating error. I have a list of projects where I want to designate "Y" if active in current year. I have start and end date columns and I my formula works when I have both the start and the end date. The problem is, I have some projects without start dates and some without end dates (from a source I can't control). I tested out a formula that takes each scenario into account and each individual formula works, BUT when combined into 1 nested IF statement, I get an error. I'd like to have one column formula that will work no matter what the date situation is. I hope the picture below explains better. (I replicated the scenario in Excel, and one nested IF formula worked for all scenarios.)

Thanks in advance for any advice!

Find more posts tagged with

Accepted answers

KevAnalyst

Val,

This may not be pretty but it's simple and you don't need to mess with a bunch of nested IFs. I would create two helper columns [Start Date Helper] [End Date Helper] (which can be hidden later and left alone once the column formulas are set).

Start Date Helper Formula: =IFERROR(YEAR(Start Date), YEAR(End Date))

End Date Helper Formula: =IFERROR(YEAR(End Date), YEAR(Start Date))

Result Formula: =IF(AND(Start Date Helper <= YEAR(TODAY()), End Date Helper >= YEAR(TODAY())), "Yes", "No")

Hidden columns so it looks clean:

Hope this helps!

Kev

All comments

KevAnalyst

Val,

Start Date Helper Formula: =IFERROR(YEAR(Start Date), YEAR(End Date))

End Date Helper Formula: =IFERROR(YEAR(End Date), YEAR(Start Date))

Result Formula: =IF(AND(Start Date Helper <= YEAR(TODAY()), End Date Helper >= YEAR(TODAY())), "Yes", "No")

Hidden columns so it looks clean:

Hope this helps!

Kev

Val V.

Kev,

Thank you SO much! Worked like a charm and solved my problem!

I did forget one scenario, I also have projects with no start OR end date, so I just added an IFERROR to the final formula:

=IFERROR(IF(AND([Start Date Helper]@row <= YEAR(TODAY()), [End Date Helper]@row >= YEAR(TODAY())), "Yes", ""), "Null").

You just made my day.

Cheers,

Val

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.