MONTH function returning #INVALID DATA TYPE because it is blank

jwilson

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?

Andrée Starå

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!

Carson Penticuff

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), "")

Andrée Starå

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!

Ramzi

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.

Cory Page

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

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

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