Library › Data AI › HR and recruiting
Get a years-of-service formula for a fixed award date
- For HR and recruiting
- You practice Giving the right context
- Time about 5 minutes
About this exercise
Service awards go to people reaching 5, 10, 15 or 20 full years as of December 31, 2026, not as of today. Ask for one formula for D2 that returns full years of service from the hire date in C2 to Dec 31, 2026, in one code block, then exactly 2 numbered lines: Why not today: and Check: (which says what Lena Ortiz's row should show). Under 50 words outside the code.
Common mistakes when you ask AI for this
- Not giving the cutoff date. The AI uses TODAY(), so anyone with an anniversary in November or December is missed. Try: Add: "Years as of December 31, 2026, not today."
- Accepting year subtraction. Subtracting years rounds people up, which is how someone got a plaque early last year. Try: Add: "Full completed years only."
- No check row. Without one known answer you can't tell whether the formula works. Try: Add: "Check: say what Lena Ortiz's row should show."
- Getting a menu of options. The AI likes to offer two or three versions of the formula, so you're left to pick and test them yourself. Try: Add: "Give one formula only, in one code block, no alternatives."
- Getting a tutorial. By default the AI explains every part of the formula at length. Try: Add: "Each line under 20 words. Nothing else."
What a strong prompt includes
- Gives the cutoff date
- Asks for full years
- Asks for a check row
- Asks for one formula
Related exercises
- Add input validation to a Java method with exact messages (Coding AI · Software developers)
- Add type hints that still run on Python 3.8 (Coding AI · Software developers)
- Announce a discount-approval process change in three bullets (Sales AI · Managers and team leads)
- Announce a hybrid work policy change with clear actions (Writing AI · HR and recruiting)
- Announce a new weekend rotation in three clear rules (Productivity AI · Managers and team leads)