Automate repetitive administration

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.