YEAR Function to look at multiple columns to pull year from

Peggy Parchert
Peggy Parchert ✭✭✭✭✭✭
edited 05/19/21 in Formulas and Functions

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.

Best Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 05/20/21 Answer ✓

    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.


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!