YEAR Function to look at multiple columns to pull year from

Is there a way to have a formula using the YEAR function to look at multiple columns until it comes to one that isn't blank?

I'm attempting to pull the Year from various project start date columns. Some lines will not have dates in a column. i basically want the formula to look in each of the columns listed below (start columns) and if one is blank, move to the next column etc etc until it comes to one that has a date in it. if there isn't a date in any of them would like it to say 'none"

My sheet has the following columns (all date columns):

Conf Start Date

Online Start Date

Private Start Date

Other Type Start Date

Year (text/number)

This is what I have for the formula and it's not working (#UNPARSEABLE error)

=IF(ISBLANK([Conf Start Date]@row), YEAR([Online Start Date]@row), YEAR([Conf Start Date]@row)), IF(ISBLANK([Online Start Date]@row), YEAR([Private Start Date]@row), YEAR([Online Start Date]@row)), IF(ISBLANK([Private Start Date]@row), YEAR([Other Type Start Date]@row), YEAR([Private Start Date]@row)), IF(ISBLANK([Other Type Start Date]@row), "none", YEAR([Other Type Start Date]@row))

Any assistance would be appreciated.

Find more posts tagged with

Accepted answers

Bassam Khalil

Hi @Peggy P

Hope you are fine if you have a different date on 2 Or 3 of your column which one you need to be selected the max between them. if so please use the following formula:

=YEAR(MAX([Conf Start]@row:[Other Type Start]@row))

the following screenshot shows the result.

Andrée Starå

Hi @Peggy P

I hope you're well and safe!

To add to Bassam's excellent advice/answer.

I've added an IFERROR part to Bassam's formula, so you don't get an error when the Year field is empty.

=IFERROR(YEAR(MAX([Conf Start]@row:[Other Type Start]@row)),"")

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

All comments

Bassam Khalil

Hi @Peggy P

Hope you are fine if you have a different date on 2 Or 3 of your column which one you need to be selected the max between them. if so please use the following formula:

=YEAR(MAX([Conf Start]@row:[Other Type Start]@row))

the following screenshot shows the result.

Andrée Starå

Hi @Peggy P

I hope you're well and safe!

To add to Bassam's excellent advice/answer.

I've added an IFERROR part to Bassam's formula, so you don't get an error when the Year field is empty.

=IFERROR(YEAR(MAX([Conf Start]@row:[Other Type Start]@row)),"")

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Peggy Parchert

@Bassam Khalil @Andrée Starå - thank you both! greatly appreciate the help!

Bassam Khalil

@Peggy P

You are welcome and I will be happy to help you any time.

Andrée Starå

@Peggy P

You're more than welcome!

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.