Find Open CRM Contacts with No Recent Follow-Up

What formula flags open CRM contacts with no recent follow-up in Google Sheets?

The Google Sheets formula below flags contacts with an ID, a status other than Closed, and either no last-contact date or a date more than 14 days old.

Assume one contact per row: column A contains the contact ID, B the last-contact date, C the status, and D the follow-up flag.

=IF(A2="","",IF(AND(C2<>"Closed",OR(B2="",B2<TODAY()-14)),"Follow up",""))

Enter the formula in D2, not a source column. Eligible rows display Follow up; other rows display an empty string. An empty-string result looks blank but still contains a formula.

Here, “open” means any status other than Closed, including blank statuses. The rule is too broad if Archived or Do not contact must also be excluded.

How do you set up the Google Sheets follow-up column and filter?

Set up the Google Sheets follow-up view by checking the source columns, entering the formula in D2, filling it down, and filtering the output.

1. Confirm the column layout. Enter Contact ID in A1, Last-contact date in B1, and Status in C1. For another layout, adjust the formula references or rearrange a working copy.

2. Add the output header. Enter Follow-up flag in D1. Keep manual notes elsewhere; entering a note in a formula cell replaces its formula.

3. Check the dates. Select column B and choose Format > Number > Date. A US-style display might show 7/5/2026. Formatting recognized dates does not reliably convert imported text into dates.

4. Enter the formula in D2. Paste the complete formula above. Row 1 contains headers, so the references start at row 2.

5. Fill down. Copy D2 through the contact rows. D3 will check A3, B3, and C3. A modest number of empty future rows can also contain the formula because missing IDs suppress flags.

6. Filter the output. Select the complete CRM range, including headers, and choose Data > Create a filter. Show only Follow up in column D. Include every CRM column so sorting preserves each contact’s information.

For a personal queue in a shared sheet, use a filter view rather than changing everyone’s filter.

What flags should the sample CRM contacts receive?

The sample CRM flags FAKE-101, FAKE-104, and FAKE-107 when TODAY() returns July 20, 2026, making July 6 the cutoff.

These fictional IDs illustrate the rule. Empty table cells represent empty cells, not the word “blank.”

Sheet row	A: Contact ID	B: Last-contact date	C: Status
2	FAKE-101	7/5/2026	Open
3	FAKE-102	7/6/2026	Open
4	FAKE-103	7/15/2026	In progress
5	FAKE-104		Open
6	FAKE-105	6/20/2026	Closed
7	FAKE-106		Closed
8	FAKE-107	6/28/2026
9		6/20/2026	Open

Enter actual Google Sheets dates: 7/5/2026 means July 5 here, not May 7. Check File > Settings for the spreadsheet locale.

With the formula filled through D9, the expected output is:

Sheet row	Contact ID	D: Follow-up flag	Reason
2	FAKE-101	Follow up	July 5 is before the July 6 cutoff.
3	FAKE-102		Exactly 14 days old, not older than 14 days.
4	FAKE-103		The contact date is recent.
5	FAKE-104	Follow up	No last-contact date is recorded.
6	FAKE-105		Closed status excludes the row.
7	FAKE-106		Closed status excludes even an undated row.
8	FAKE-107	Follow up	Blank status is not Closed, and the date is old.
9			Missing contact ID suppresses the flag.

Filtering D to Follow up hides other rows without deleting them. On another day, TODAY() changes the cutoff automatically.

Why do nested IF, AND, and OR flag contacts needing follow-up?

Nested IF, AND, and OR flag contacts by requiring an ID and a non-Closed status while accepting either a missing date or a date older than 14 days.

The outer IF first checks A2="" and returns "" when no ID exists. This gate keeps unused rows quiet rather than evaluating every row as a contact. For rows with IDs, the inner IF uses AND to require both status eligibility and date eligibility, rather than allowing either condition alone to trigger a reminder.

Within that test, C2<>"Closed" implements the guide’s broad definition of open instead of requiring the literal status Open. Next, OR accepts B2="" or B2<TODAY()-14, allowing missing dates and old dates as separate qualifying paths. TODAY() supplies a moving cutoff rather than a fixed date. The strict < excludes July 6 when today is July 20, while July 5 qualifies.

Finally, the inner IF converts the combined logical result into "Follow up" or "", providing a readable filter label instead of TRUE or FALSE.

How does the strict 14-day cutoff treat IDs, statuses, and dates?

The follow-up formula requires a contact ID, excludes Closed, and flags missing dates or dates strictly before TODAY()-14.

The outer condition, IF(A2="","",...), suppresses reminders for unused rows and populated records missing an ID. Treat the ID as required.

The status condition, C2<>"Closed", is not case-sensitive in standard Google Sheets comparisons: closed also matches Closed. Extra spaces can prevent that match.

The date condition is:

OR(B2="",B2<TODAY()-14)

Checking for an empty date explicitly includes contacts without recorded activity. On July 20, July 6 is exactly 14 days old and remains unmarked; July 5 qualifies.

For a “14 days or more” policy, replace B2<TODAY()-14 with B2<=TODAY()-14 in the main formula.

TODAY() supplies a calendar date, not the current time. Stored timestamps can behave differently from elapsed-hours reminders; this is not an exact 336-hour countdown.

How do you fix incorrect Google Sheets follow-up flags?

Fix incorrect follow-up flags by checking date values, status whitespace, missing IDs, apparent blanks, and spreadsheet time settings.

An old imported date does not produce a flag

Symptom: An eligible contact’s visibly old date produces no reminder.

Cause: The date may be text; date formatting alone does not convert it.

Exact fix: Enter =ISNUMBER(B2) in an unused cell. A real Sheets date should return TRUE. For a nonempty text date returning FALSE, confirm the locale and enter =DATEVALUE(B2) in a temporary cell. Copy the result and use Paste special > Values only over B2, then apply Date formatting.

Convert only affected, nonempty text-date cells. If conversion fails, enter an unambiguous date such as =DATE(2026,7,5) temporarily and paste its value over B2.

A Closed contact still shows Follow up

Symptom: A row appears Closed but remains flagged.

Cause: Trailing spaces or imported nonbreaking spaces can prevent a match.

Exact fix: Enter this in a temporary cell:

=TRIM(SUBSTITUTE(C2,CHAR(160)," "))

Copy the cleaned result and paste values only over C2, then remove the temporary formula. For future entries, use Data > Data validation to add a status dropdown and reject unapproved values. Clean the text only when the intended status is Closed.

A contact with no date is not flagged

Symptom: Column B looks empty, but an otherwise eligible row has no flag.

Cause: B may contain spaces, or A may lack an ID.

Exact fix: Confirm a valid contact ID. For a genuinely unknown date, select the B cell and press Delete. Do not use spaces or dashes as placeholders. If another formula supplies the date, make that formula return "" for unknown dates.

Today’s queue appears to use yesterday’s cutoff

Symptom: A contact expected to become overdue today remains unmarked.

Cause: Time zone or recalculation settings may differ from the expected workday.

Exact fix: Open File > Settings and confirm the time zone. Under Calculation, select a periodic setting such as On change and every hour. This updates a calendar-date rule, not a scheduled notification.

How should contact activity update the follow-up queue?

Update column B after qualifying contact activity rather than manually erasing Follow up from column D.

Erasing the flag removes the formula. Define what counts as contact: an internal note should not reset a date intended to track outreach.

An undated flag means “no contact date recorded,” not proof that no contact occurred. Review available history before contacting the person, particularly when multiple teammates maintain records.

When should you avoid this last-contact-date follow-up method?

Avoid this Google Sheets method when contacts have multiple independent deadlines, outreach restrictions, or requirements for automatic notifications.

• Multiple commitments: Use a task table with one row and due date per commitment, or CRM tasks linked to contacts.
• Automatic reminders: Use a dedicated notification workflow; a formula labels rows without sending messages or assigning tasks.
• Restricted statuses: Build an explicit eligibility rule before using the queue if non-Closed statuses can prohibit outreach.