# YEAR Function to look at multiple columns to pull year from

Options
✭✭✭✭✭✭
edited 05/19/21

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.

• ✭✭✭✭✭✭
Options

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.

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

Hi @Peggy P

I hope you're well and safe!

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

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.

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

Hi @Peggy P

I hope you're well and safe!

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

You're more than welcome!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!