Build Excel and Google Sheets Formulas for Workdays, Ages and Deadlines

You get working date formulas for your exact layout, such as workdays, deadlines, ages and month offsets, with a holiday table, locale pitfalls and a test row.

Prompt

Prompt
Act as a spreadsheet analyst who builds date and time formulas in Excel and Google Sheets.

Your job: write working formulas for my date calculations, such as workdays between dates, ages, deadlines, due dates a set number of months out, or hours between timestamps.

Before writing anything, ask me these questions in one message and wait for my answers:
1. Excel (which version) or Google Sheets, and what country or locale is the file set to?
2. What do you need to calculate? Describe the columns and give one example row.
3. Do weekends or public holidays matter, and which days count as the weekend?
4. Do any times come from different time zones or systems?

Rules:
- Use NETWORKDAYS.INTL, WORKDAY.INTL, EDATE, EOMONTH, DATEDIF or YEARFRAC where they fit, and point to a named holiday range instead of typing dates into formulas.
- Write each formula with cell references that match my layout, and state whether it counts the start date.
- Use the argument separator my locale needs (comma or semicolon).
- Flag every date-format or time-zone pitfall that applies, such as text that looks like a date, DD/MM versus MM/DD, DATEDIF quirks and daylight saving shifts.
- Include one test row with the expected result so I can check each formula.

Output format:
1. Setup (holiday table, named ranges, cell formats)
2. Formula table: Purpose | Cell | Formula | Example result
3. Pitfalls to check
4. Test row

End with one line naming any assumption you made about my data.

Example output

At a glanceTest row results for Priya MalikFormula outputs as at 14/04/2025 with England bank holidays
Deadline after 10 working days
30/04/2025
WORKDAY.INTL from start 14/04/2025
Contract end
13/10/2025
EDATE(C2,6)-1
Age in whole years
34
DATEDIF from 22/09/1990
Working days left
11
NETWORKDAYS.INTL to deadline

Example for: a UK operations coordinator in Excel 365 (UK locale) tracking task deadlines, contract ends and staff ages, Monday to Friday week, England bank holidays.

1. Setup

  • Create a sheet called Holidays with 2025 England and Wales bank holidays in A2:A9: 01/01/2025, 18/04/2025, 21/04/2025, 05/05/2025, 26/05/2025, 25/08/2025, 25/12/2025, 26/12/2025.
  • Select A2:A9 and name it Holidays (Formulas > Define Name).
  • Format columns B, C, E and F as dd/mm/yyyy. Format G and H as General.
  • Layout on the Tasks sheet: A Name, B Date of birth, C Start date, D Working days allowed, E Deadline, F Contract end, G Age, H Working days left.

2. Formula table

PurposeCellFormulaExample result
Deadline after N working days (start date not counted)E2=WORKDAY.INTL(C2,D2,1,Holidays)30/04/2025
Contract end, 6 months minus one dayF2=EDATE(C2,6)-113/10/2025
Age in whole years todayG2=DATEDIF(B2,TODAY(),"Y")34 (as at 14/04/2025)
Working days left, today and deadline both countedH2=NETWORKDAYS.INTL(TODAY(),E2,1,Holidays)11 (as at 14/04/2025)

The weekend code 1 means Saturday and Sunday off. UK Excel uses commas as separators, so no change is needed.

3. Pitfalls to check

  • Text dates from CSV imports: run =ISNUMBER(C2). FALSE means the value is text and every formula will fail or return #VALUE!. Fix it with Data > Text to Columns > Date: DMY.
  • US-format exports: dates like 04/05/2025 from a US system may mean 5 April, not 4 May. Check any date where the day is 12 or lower.
  • DATEDIF is hidden: it will not appear in autocomplete, but it works. Avoid the "MD" unit, which can return wrong results.
  • Holiday range: the dates must be real dates, not text, and must cover every year your deadlines reach. Add 2026 before October.
  • Daylight saving: if you subtract timestamps, clocks change on 30/03/2025 and 26/10/2025, so a shift that crosses either date is one hour off.

4. Test row

NameDOBStartDaysDeadlineContract endAgeLeft
Priya Malik22/09/199014/04/20251030/04/202513/10/20253411

Assumption: working days allowed start the day after the start date, and Good Friday and Easter Monday are non-working days.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Asking for locale first gets the right separator and date order, which causes most broken date formulas.
  • A named holiday range keeps formulas short and easy to update each year instead of hard-coding dates.
  • The test row with expected results lets you confirm each formula works before filling it down.

Use this in ChatGPT, Claude or Gemini when you need deadline, workday or age columns. Have your column layout, one sample row, your locale and your public holiday list ready.

Common edits

  • Change the weekend code, for example to 7 for a Friday and Saturday weekend.
  • Ask for hours worked between timestamps, including overnight shifts that cross midnight.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets