Use Google Sheets AI to Build an IEP Deadline Tracker

What This Does

Google Sheets can automatically calculate IEP anniversary dates, flag upcoming deadlines in red, and keep your entire caseload organized — using AI to help you write the formulas without knowing any spreadsheet code.

Before You Start

• You have Google Sheets open (sheets.google.com)
• You're signed into your Google account
• You have your students' IEP start dates (or can look them up)

Steps

1. Create a new spreadsheet

Go to sheets.google.com and click + Blank to create a new spreadsheet. Name it something like "Caseload Tracker 2025-26."

2. Set up your column headers

In Row 1, type these headers across the columns:

• A1: Student ID (use numbers, not names — keeps it FERPA-friendly)
• B1: Grade
• C1: Disability Category
• D1: IEP Start Date
• E1: Annual Review Due
• F1: Re-Evaluation Due
• G1: Days Until Annual Review
• H1: Status

3. Enter your student data

Fill in columns A through D for each student. In column D, enter IEP dates in MM/DD/YYYY format.

4. Use Sheets AI to write your formulas

Click on cell E2 (Annual Review Due). Look for the Help me with formulas or Explore button in the bottom right corner of Sheets, or try typing = in the cell and then clicking the AI or sparkle icon if it appears.

If you see a formula bar AI prompt, type:

Calculate the date that is exactly 1 year from the date in cell D2

The formula should be: =DATE(YEAR(D2)+1, MONTH(D2), DAY(D2))

For Re-Evaluation Due (column F, every 3 years): =DATE(YEAR(D2)+3, MONTH(D2), DAY(D2))

Troubleshooting: If the AI formula helper isn't appearing, just type the formula directly — it's simple enough to copy.

5. Add days-until countdown in column G

Click G2 and enter: =E2-TODAY()

Format this column as a number (not a date). Now you can see exactly how many days until each student's IEP is due.

6. Add color-coded status with conditional formatting

• Select column G (or E) → click Format → Conditional formatting
• Add rule: "Less than 30" → set background to Red
• Add rule: "Less than 60" → set background to Yellow
• Add rule: "Greater than 60" → set background to Green

What you should see: Your upcoming deadlines now turn red when they're within 30 days — no more missing IEP anniversaries.

7. Extend formulas to all rows

Click cells E2:H2, then drag the blue handle in the bottom-right corner down to cover all your students.

Real Example

Scenario: You have 22 students on your caseload. You want to see at a glance which IEPs are due in the next 6 weeks without scrolling through individual files.

Setup: After building the tracker, sort column G (Days Until Annual Review) ascending. The students closest to their IEP anniversary will appear at the top in red — you can immediately see who to prioritize.

What you get: A live dashboard that updates every day. Share it with your SPED coordinator (View only) so they can monitor compliance without asking you.

Tips

• Add a Notes column (column I) where you can flag "meeting scheduled," "parent unreachable," or "evaluation in progress" — this becomes your running record
• Use Google Sheets AI's Explore sidebar (bottom right) to ask natural language questions like "which students have a review due in the next 30 days" and it will filter for you
• Add your re-evaluation due dates too — a 3-year eval requires 60+ days to complete properly, so early warning matters even more than for annual reviews

Tool interfaces change — if a button has moved, look for similar AI/magic/smart options in the same menu area.