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

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!

Find more posts tagged with

Sheets

Formulas

Accepted answers

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)

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.