Find Open CRM Contacts with No Recent Follow-Up
How do I flag Google Sheets CRM contacts with no last-contact date or more than 14 days without follow-up while excluding Closed contacts?
Add a formula column that checks the contact ID, status, and last-contact date, then filter for Follow up. The formula flags rows with an ID, a status other than Closed, and either no last-contact date or a date more than 14 days old.
What formula flags open CRM contacts with no recent follow-up?
Use a formula that requires a contact ID, excludes Closed, and flags a row when its last-contact date is empty or more than 14 days old. Put contact IDs in column A, last-contact dates in B, statuses in C, and the follow-up flag in D.
=IF(A2="","",IF(AND(C2<>"Closed",OR(B2="",B2<TODAY()-14)),"Follow up",""))
Enter the formula in D2, not in 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 a blank status. That rule is too broad if statuses such as Archived or Do not contact must also prevent outreach.
How do I set up the Google Sheets follow-up column and filter?
Set up the follow-up view by checking the source columns, entering the formula in D2, filling it down, and filtering the output.
-
Confirm the column layout. Enter
Contact IDin A1,Last-contact datein B1, andStatusin C1. If your CRM uses another layout, adjust the formula references or rearrange a working copy. -
Add the output header. Enter
Follow-up flagin D1. Keep manual notes elsewhere; typing a note in a formula cell replaces its formula. -
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. -
Enter the formula in D2. Paste the complete formula above. Row 1 contains headers, so the references start at row 2.
-
Fill down. Copy D2 through the contact rows. D3 will check A3, B3, and C3. You can also fill a modest number of empty future rows because missing IDs suppress flags.
-
Filter the output. Select the complete CRM range, including headers, and choose Data > Create a filter. Show only
Follow upin column D. Include every CRM column so sorting keeps each contact’s information together.
For a personal queue in a shared sheet, use a filter view rather than changing everyone’s filter. If the CRM also needs checks for missing ownership or next steps, see how to flag CRM contacts missing an owner or next step.
What flags should the sample CRM contacts receive?
The sample flags FAKE-101, FAKE-104, and FAKE-107 when TODAY() returns July 20, 2026, making July 6 the cutoff. The TODAY function supplies the moving date used in that comparison.
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. |
The cutoff arithmetic is July 20 minus 14 calendar days, which gives July 6. July 5 is earlier and qualifies; July 6 is exactly 14 days old and does not. Filtering D to Follow up hides other rows without deleting them. The cutoff changes as TODAY() changes.
How does the strict 14-day cutoff treat IDs, statuses, and dates?
The formula requires a contact ID, excludes Closed, and flags missing dates or dates strictly before TODAY()-14. The AND function combines the status and date tests so both conditions must be true.
The outer IF(A2="","",...) suppresses reminders for unused rows and records missing an ID. Treat the ID as required. The status test, C2<>"Closed", uses the guide’s broad definition of open rather than requiring the literal status Open. Extra spaces can prevent a match.
The date test is:
OR(B2="",B2<TODAY()-14)
The explicit empty-date check 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 an elapsed-hours reminder, so this is not an exact 336-hour countdown.
How do I fix incorrect Google Sheets follow-up flags?
Check date values, status whitespace, missing IDs, apparent blanks, and spreadsheet time settings when a row’s flag does not match the intended rule.
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. For a related contact-data task, see how to normalize CRM email addresses before a CSV import.
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.
When should I avoid this last-contact-date follow-up method?
Avoid this method when contacts have multiple independent deadlines, outreach restrictions, or requirements for automatic notifications. A single last-contact date cannot represent separate commitments, and this formula labels rows rather than sending messages or creating assigned tasks.
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 someone, particularly when multiple teammates maintain records.
Use another method for these cases:
- Multiple commitments: Use a task table with one row and due date per commitment, or CRM tasks linked to contacts.
- Automatic reminders: Use a separate notification workflow if someone needs a message or assigned task.
- Restricted statuses: Write an explicit eligibility rule before using the queue if non-Closed statuses can prohibit outreach.
This kind of spreadsheet rule belongs with the other practical tools in the small-business stack.