eliminate INVALID DATA TYPE when using a IF statement to display the month

thunt06

I am using an IF statement to display the month but when I don't have a date the column is displaying #INVALID DATA TYPE.  I understand why this is happening but I don't know if I can also incorporate the use of an IFERROR statement nested. What is the correct way to eliminate the #INVALID DATA TYPE?

Here is my IF Statement that works with all the dates but displays the INVALID DATA TYPE when there is NO date.

=IF(MONTH(Completed74) = 1, "January", IF(MONTH(Completed74) = 2, "February", IF(MONTH(Completed74) = 3, "March", IF(MONTH(Completed74) = 4, "April", IF(MONTH(Completed74) = 5, "May", IF(MONTH(Completed74) = 6, "June", IF(MONTH(Completed74) = 7, "July", IF(MONTH(Completed74) = 8, "August", IF(MONTH(Completed74) = 9, "September", IF(MONTH(Completed74) = 10, "October", IF(MONTH(Completed74) = 11, "November", IF(MONTH(Completed74) = 12, "December"))))))))))))

 

Chak Khiam

For simplicity, let X be your formula above

Wrap the IFERROR around X and put in what do you want to be displayed if there is an error:

=IFERROR(X, anything that is appropriate)

"anything that is appropriate" could be a text or number such as "Enter Date" or simply 0

 

PS:

In this case, you might want to use the @row formula for efficiency, eg:

MONTH(Completed74) = 11

with

MONTH(Completed@row) = 11

Paul Newcome

You could also just use an additional IF statement at the beginning to say that if there is no date, leave it blank.

 

=IF(ISBLANK(Completed@row), "", rest of your formula)

.

Or alternatively, you can say that if it is a date, run your already established formula.

=IF(ISDATE(Completed@row), rest of your formula)

thunt06

Thank you for the simple fix.  I kept trying other ways but that is the one that worked.  

BTW, I tried to your suggestion with incorporating the @row and that worked as well.  What is the benefit of using an @row instead of the row number?

Thank you

 

Paul Newcome

@row simply tells the formula to look at the column on the same row as the formula. Some of the benefits include increased efficiency on the back end of things, improved accuracy due to being less likely to accidentally type in the wrong row number, copy/paste and dragfilling are much easier as well.

thunt06

Thank you for providing simple alternatives. I have tried each one to ensure that I am understanding the use.  Everything worked.  Thanks again

thunt06

Thank you for the explanation.  

Paul Newcome

Sure thing.

Paul Newcome

Happy to help!