MONTH function returning #INVALID DATA TYPE because it is blank

Options

How can I use the MONTH function if the cell is blank? It is returning #INVALID DATA TYPE.

I'm pulling this information into 3 columns from the Actual Submission Date Column:

"Month" - new column with the following formula: =MONTH(([Actual Submission Date]@row))

"Year" - new column with the following formula: =YEAR([Actual Submission Date]@row)

"Submitted MM/YYYY" - new column with the following formula: =Month@row + "/" + Year@row

These are date columns and I want to pull the Month and Year only into one cell for a monthly report.

Is there a better way of doing this?

Best Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 08/08/23 Answer ✓
    Options

    Hi @jwilson

    I hope you're well and safe!

    Try something like this.

    Edited because I was thinking of a cross-sheet range.

    Try this.

    =IFERROR(MONTH([Actual Submission Date]@row), "")

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. 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: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.

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓
    Options

    Are you using the Month and Year columns simply as a way to pull out that information to fill in the MM/YYYY column? If so, you can eliminate those columns entirely and directly pull the data into the MM/YYYY column.

    =IFERROR(IF(MONTH([Actual Submission Date]@row) < 10, "0", "") + MONTH([Actual Submission Date]@row) + "/" + YEAR([Actual Submission Date]@row), "")

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 08/08/23 Answer ✓
    Options

    Hi @jwilson

    I hope you're well and safe!

    Try something like this.

    Edited because I was thinking of a cross-sheet range.

    Try this.

    =IFERROR(MONTH([Actual Submission Date]@row), "")

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. 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: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.

  • Ramzi
    Ramzi ✭✭✭✭✭
    Options

    Try wrapping your formulas with IFERROR

    Example: =IFERROR(MONTH(([Actual Submission Date]@row)),"")

    Or - use an IF statement:

    Example: IF(ISBLANK([Actual Submission Date]@row),"",MONTH(([Actual Submission Date]@row)))


    I hope this helps you.

    Smartsheet Solutions Architect

    www.adapture.com

  • Cory Page
    Cory Page ✭✭✭✭✭
    Options


    Are you simply looking to leave a the month column blank if there is no date? A quick fix could be to simply drop an iferror in front..


    =IFERROR(MONTH([Date of service]@row), "")


  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓
    Options

    Are you using the Month and Year columns simply as a way to pull out that information to fill in the MM/YYYY column? If so, you can eliminate those columns entirely and directly pull the data into the MM/YYYY column.

    =IFERROR(IF(MONTH([Actual Submission Date]@row) < 10, "0", "") + MONTH([Actual Submission Date]@row) + "/" + YEAR([Actual Submission Date]@row), "")

  • jwilson
    jwilson ✭✭✭
    Options

    So this works fine:

    =IFERROR(IF(MONTH([Actual Submission Date]@row) < 10, "0", "") + MONTH([Actual Submission Date]@row) + "/" + YEAR([Actual Submission Date]@row), "")

    If the Date has text in the cell (Pending, On Hold, Cancelled), I would like the text returned.

    Example:

    07/2023

    08/2023

    Pending

    Cancelled

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!