Write color rules for expiring certifications

About this exercise

You want the Certifications sheet (A2:E60) to color whole rows red when a certification has expired and yellow when it expires within the next 30 days, and rows with a blank expiry date must stay uncolored. Ask for exactly 3 numbered lines: Red: with its custom formula, Yellow: with its custom formula, and Apply to: with the range. Plain text, under 70 words.

Common mistakes when you ask AI for this

• Forgetting blank cells. A blank date counts as "before today", so the AI's formula turns every no-cert row red. Try: Add: "Blank expiry dates must stay uncolored."
• Coloring one cell, not the row. The AI writes =D2<TODAY(), which only colors properly in column D. Try: Add: "Color whole rows across A2:E60; lock the column with $D2."
• Getting menu steps. The AI explains where to click in the Format menu instead of giving the rules. Try: Say: "Exactly 3 lines: Red:, Yellow:, Apply to:."
• Getting markdown back. AI bolds the color names and adds headings. Try: Ask for "plain text, no bold."

What a strong prompt includes

• Handles blank dates
• Colors whole rows
• Gives the 30-day window
• Names the 3 lines
• Asks for plain text

Practice this now ↑

Related exercises

• Accept the buyer's counteroffer on three stated conditions (Sales AI · Sales and customer success)
• Accrue goods received but not invoiced at PO price (Finance AI · Accountants)
• Add dark mode by overriding your existing CSS variables (Coding AI · Software developers)
• Add input checks to your number guessing game (Coding AI · High school students)
• Add labels to a signup form without rewriting the page (Coding AI · High school students)