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

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

Find more posts tagged with

Formulas

IT & Operations

Media & Entertainment

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…