Library › Coding AI › HR and recruiting

Show employee tenure as years and months

Practice: Asking for a format you can use. About 5 minutes.

About this exercise

Write one Excel formula for Staff!E2 (filled down) that shows tenure from the start date in C2 to the review date in $H$1 (not today) as text exactly like 4y 7m. New hires whose start date is after $H$1 must show Not started. One formula in one code block, then under 25 words outside the code.

Common mistakes when you ask AI for this

  • Not giving the review date. The AI uses TODAY(), so tenure in the letters changes daily. Try: Add: "Measure to the review date in $H$1."
  • Not showing the format. The AI returns 4.6 years or "4 years, 7 months". Try: Add an example: "exactly like 4y 7m".
  • Forgetting future hires. DATEDIF errors when the start date is after the end date. Try: Add: "If C2 is after $H$1, show Not started."
  • 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 code at length, which you then have to scroll past. Try: Add: "After the code, one line under 10 words. Nothing else."

What a strong prompt includes

  • Uses the review date
  • Shows the format
  • Handles future hires
  • Asks for one answer only
  • Limits the explanation

Practice this now ↑

Related exercises