How to mark column with 1 or 0 if the month is in a date

CLBai

Hi,

I am trying to track whether or not someone has been active in a month based on their Last Login date. I created the formula to mark a cell as 1 if the month is in the Last Login date or 0 if it is not:

=IF(MONTH([Last Login]@row), 1, 0)

But the problem is it gives an #INVALID DATA TYPE error if it hits a blank date. How do I adjust the formula to put a 0 when there is a blank date like the example screenshot?

Thanks!

Jeff Reisman

@CLBai You have two ways to make this work. You can either suppress/replace the error message, or you can prevent the error in the first place. Here's how:

To suppress or replace error messages from any formula, wrap it in IFERROR. The syntax is =IFERROR(formula goes here), value to replace the error message with)

=IFERROR(IF(MONTH([Last Login]@row), 1, 0), 0)

The above will replace the error with a zero

To prevent errors from blank date cells, you can use an IF to tell the system to only run the formula if that value is a date, or use AND to make that check part of the logical expression.

=IF(ISDATE([Last Login]@row), IF(MONTH([Last Login]@row), 1, 0), 0)

=IF(AND(ISDATE([Last Login]@row), IF(MONTH([Last Login]@row)), 1, 0)

Paul Newcome

You would wrap the whole thing in an IFERROR.

=IFERROR(IF(MONTH([Last Login]@row), 1, 0), 0)

Jeff Reisman

@CLBai You have two ways to make this work. You can either suppress/replace the error message, or you can prevent the error in the first place. Here's how:

To suppress or replace error messages from any formula, wrap it in IFERROR. The syntax is =IFERROR(formula goes here), value to replace the error message with)

=IFERROR(IF(MONTH([Last Login]@row), 1, 0), 0)

The above will replace the error with a zero

To prevent errors from blank date cells, you can use an IF to tell the system to only run the formula if that value is a date, or use AND to make that check part of the logical expression.

=IF(ISDATE([Last Login]@row), IF(MONTH([Last Login]@row), 1, 0), 0)

=IF(AND(ISDATE([Last Login]@row), IF(MONTH([Last Login]@row)), 1, 0)

CLBai

Perfect! Thank you so much!