Creating a IF(AND(ISDATE formula to check a box

Hi All,

I am trying to write a IF(AND(ISDATE formula to check a box I have read through several post but dont see and example of doing all three of these functions nested together. I get either unparsable or Invalid argument on my attempts.

Goal: in my data set I dont care what the date is just that it is a date (anydate) I am using a Vlookup in COLUMN2 to pull in the dates, and the rows with out matching Vlookup partners come back with #NO MATCH so I am trying to get those rows with part 2 of the formula. If COLUMN1 AND 2 dont meet the criteria then dont check the box.

If COLUMN1 = Foreign and COLUMN2 = a date then check the box

If COLUMN1 = Foreign and COLUMN2 = not a date then dont check the box

Others wise dont check the box.

Thanks

Lisa

Find more posts tagged with

Formulas

functionality

Accepted answers

Paul Newcome

Try something like this...

=IF(AND([Column1]@row = "Foreign", ISDATE([Column2]@row)), 1)

All comments

Andrée Starå

Hi Lisa,

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic weekend & Happy New Year!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

Try something like this...

=IF(AND([Column1]@row = "Foreign", ISDATE([Column2]@row)), 1)

NikkiOno

@Paul Newcome

Hi again, I need to do something similar but my formula isn't working. I have one sheet with employees name (database) and another sheet (survey responses) where I need a column to be checked IF both names match and there is a date in the termination date column on the database. What am I doing wrong?

=IF(AND({EMPLOYEE NAME} = [FIRST & LAST NAME]@row, ISDATE({TERMINATION DATE})), 1)

Paul Newcome

@NikkiOno Are you able to provide screenshots for reference?

NikkiOno

@Paul Newcome

PAST EMPLOYEES - SHEET 1

Screen Shot 2022-10-20 at 2.00.24 PM.png

EMPLOYEE SURVEY - SHEET 2

Screen Shot 2022-10-20 at 1.59.06 PM.png

Screen Shot 2022-10-20 at 2.01.40 PM.png

Paul Newcome

@NikkiOno You would do something more like this:

=IF(COUNTIFS({Employee Name}, @cell = [Employee Name]@row, {Termination Date}, @cell <> "")> 0, 1)

This counts how many rows have the employee's name and a termination date then says if that count is greater than zero, check the box.

NikkiOno

@Paul Newcome That worked! Thanks so much.

Paul Newcome

@NikkiOno Happy to help. 👍️

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.