# Day 5: Automate repetitive administration

Create and test a spreadsheet rule that flags overdue tasks.

Let AI help explain a simple rule, then let a spreadsheet repeat it. Start with a small tracker you can understand and check.

## Steps

1. Write one clear rule

Use the formula prompt to define columns and status rules. The AI designs and explains the formula; it does not connect systems.

2. Create the practice sheet

Use A1 Task, B1 Due date, C1 Progress, D1 Status, E1 Reference date. Put =DATE(2026,10,14) in F1. Enter fictional dates as actual date values, using =DATE(year,month,day) if needed.

3. Add the status formula

Paste =IF(C2="Done","Done",IF(B2="","",IF(B2<$F$1,"Overdue","Open"))) into D2 and fill down. F1 stays fixed because of the dollar signs. English function syntax and separators may depend on locale settings.

4. Test the results

At 14/10/2026: unfinished due 13/10 → Overdue; unfinished due 16/10 → Open; completed due 12/10 → Done; unfinished no date → blank; unfinished due 14/10 → Open; completed no date → Done.

5. Switch to the current date

After testing, replace F1 with =TODAY(). It uses the current date when the spreadsheet recalculates. Check timezone/date settings and review the list daily. This does not send reminders or emails.

## Understand the method

Our fictional admin list has four items: draft a quote, check materials, issue an agreed invoice, and confirm an appointment. Each has a due date and a progress field. We want the status to show Done for completed tasks, Overdue for unfinished tasks whose date has passed, Open for other dated tasks, and blank where an unfinished task has no due date.

Open ChatGPT and use today's formula prompt. Tell it exactly which columns contain the data. Ask for a Google Sheets formula, a plain-English explanation and test cases. For the exercise, the reference date goes in cell F1. Using a fixed reference date makes the worked example reproducible, regardless of when you watch the lesson.

Open a blank Google Sheet using an account you're authorised to use. Create four headings: Task, Due date, Progress and Status. Put Reference date in E1. In F1 enter equals DATE, open bracket, twenty twenty-six, comma, ten, comma, fourteen, close bracket. The written formula is on the lesson page, so you can copy it accurately.

Enter the four fictional rows. Make sure the due-date cells are actual dates, rather than text that merely looks like a date. In D2 paste the supplied IF formula and fill it down. The rule checks Done first, then blank dates, then whether the due date is earlier than F1. The dollar signs keep that reference fixed as you fill down.

For a reference date of fourteen October twenty twenty-six, the first example should be Overdue, the second Open, the third Done, and the fourth blank. Those are expected test results, not a claim that a software recording has already been made. When recording, show the actual sheet and investigate any result that doesn't match.

Test the edge cases too. A due date equal to the reference date should be Open. A completed task should be Done even if it has no date. An unfinished blank date should stay blank. Change one progress value and check that the status follows the rule. If your account uses different formula separators, follow your sheet's locale settings.

Once the rule passes, replace the fixed date in F1 with equals TODAY, open bracket, close bracket. That uses the current date when the sheet recalculates. Check the spreadsheet timezone and date settings. This is a status tracker: it doesn't send reminders or run a background email sequence. Any customer communication still needs your review.

## Copy-and-use prompt

```text
Help me create a simple Google Sheets admin-status formula. Do not connect accounts or run automation.
A = Task. B = Due date (real date values or blank). C = Progress (Open or Done). D = Status.
F1 contains a reference date.
Rules, in this order: if C2 is Done, return Done; otherwise if B2 is blank, return blank; otherwise if B2 is earlier than F1, return Overdue; otherwise return Open. A task due on the reference date is Open.
Give the formula for D2 with F1 locked so I can fill down, explain it in plain English, and give six test cases including completed/no date and due today. Use English function names. Mention possible locale separators. Do not write an email-sending script or suggest a paid integration. Flag assumptions.
```

## Illustrative example

Fictional Moss & Co admin task list

Input:
Reference date 14/10/2026. Draft quote: due 13/10, Open. Check materials: due 16/10, Open. Issue agreed invoice: due 12/10, Done. Confirm appointment: no date, Open.

Illustrative draft (not a recorded software result):
Expected statuses: Draft quote → Overdue. Check materials → Open. Issue agreed invoice → Done. Confirm appointment → blank. Formula: =IF(C2="Done","Done",IF(B2="","",IF(B2<$F$1,"Overdue","Open")))

## Your task

Build the four-row practice tracker, check all six test cases and choose a daily review time. Use your existing account; no paid automation service is required for the exercise.

## Check before completion

- Due-date cells contain date values.
- Completed tasks show Done.
- Unfinished past dates show Overdue.
- Today/future dates show Open.
- Unfinished blank dates stay blank.
- F1 remains fixed when formulas are filled down.
- You understand the rule and have a review routine.

## Watch out for

- Dates stored as text can cause incorrect comparisons.
- Different spreadsheet locales may need different separators.
- TODAY reflects the date at recalculation and is not a background scheduler.
- A formula is not an inbox connection or automatic reminder service.
- Back up important spreadsheets before changing formulas.

Ordinary tool link: https://sheets.google.com/