HOW TO USE IF BLANK WITH THIS FORMULA

Question

I am using this formula to return the month and year from a reference date, but cannot figure out how to use IFBLANK with it in order to return a blank cell if the reference date cell is empty. This is the formula I am using and trying to add IFBLANK to.

=IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 1, "01-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 2, "02-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 3, "03-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 4, "04-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 5, "05-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 6, "06-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 7, "07-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 8, "08-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 9, "09-", IF(MONTH([Floor Production FINISH (Actual)]@row) = 10, "10-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 11, "11-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 12, "12-")))))))))))) + YEAR([WALL DESIGN FINISH (ACTUAL)]@row)

jmyzk_cloudsmart_jp · Answer

I forgot to check that the YEAR function receives a non-blank value.

=IF(ISBLANK([WALL DESIGN FINISH (ACTUAL)]@row), "", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 1, "01-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 2, "02-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 3, "03-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 4, "04-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 5, "05-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 6, "06-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 7, "07-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 8, "08-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 9, "09-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 10, "10-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 11, "11-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 12, "12-"))))))))))))) + 
IF(ISBLANK([WALL DESIGN FINISH (ACTUAL)]@row), "", YEAR([WALL DESIGN FINISH (ACTUAL)]@row))

or

=IF(ISBLANK([WALL DESIGN FINISH (ACTUAL)]@row), "", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 1, "01-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 2, "02-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 3, "03-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 4, "04-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 5, "05-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 6, "06-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 7, "07-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 8, "08-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 9, "09-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 10, "10-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 11, "11-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 12, "12-")))))))))))) 
+ YEAR([WALL DESIGN FINISH (ACTUAL)]@row))

The NOT ISBLANK check is OK most of the time, but if you have some non-date value in the cell, the formula will get #INVALID DATA TYPE error.

So, to ensure you apply the MONTH or YEAR function to a date value, you can use the ISDATE function.

=IF(ISDATE([WALL DESIGN FINISH (ACTUAL)]@row), 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 1, "01-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 2, "02-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 3, "03-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 4, "04-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 5, "05-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 6, "06-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 7, "07-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 8, "08-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 9, "09-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 10, "10-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 11, "11-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 12, "12-"))))))))))))
+ YEAR([WALL DESIGN FINISH (ACTUAL)]@row)

or

=IF(ISDATE([WALL DESIGN FINISH (ACTUAL)]@row), IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 1, "01-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 2, "02-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 3, "03-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 4, "04-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 5, "05-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 6, "06-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 7, "07-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 8, "08-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 9, "09-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 10, "10-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 11, "11-", IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 12, "12-")))))))))))) + YEAR([WALL DESIGN FINISH (ACTUAL)]@row))

Trinity Knittle · Answer

I copied and pasted this formula you gave and it still says #INVALID OPERATION in the rows without a date in the reference cell

jmyzk_cloudsmart_jp · Answer

Hi @Trinity Knittle

You can put the IF(ISBLANK(),"", in front of your formula.

=IF(ISBLANK([WALL DESIGN FINISH (ACTUAL)]@row), "", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 1, "01-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 2, "02-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 3, "03-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 4, "04-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 5, "05-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 6, "06-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 7, "07-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 8, "08-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 9, "09-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 10, "10-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 11, "11-", 
IF(MONTH([WALL DESIGN FINISH (ACTUAL)]@row) = 12, "12-")))))))))))))
 + YEAR([WALL DESIGN FINISH (ACTUAL)]@row)

https://app.smartsheet.com/b/publish?EQBCT=de5c76d022b74641802a09436470ca20