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
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
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 like this
Build an Invoice Template in Excel or Google Sheets with Automatic Totals
You get a cell-by-cell invoice layout with exact formulas for line totals, discount, tax, due date, and a duplicate-proof invoice numbering system.
Clean and Standardize Messy Spreadsheet Data with Exact Formulas
Get a column-by-column cleaning plan with ready-to-fill formulas for spaces, case, dates and duplicates, plus a list of rows that need a manual check.
Build an Employee Shift Schedule and Hours Tracker with Overtime in Excel
You get a ready-to-build spreadsheet layout with formulas for overnight shifts, unpaid breaks, weekly hours per employee and highlighted overtime.
Build an Inventory Tracker Spreadsheet with Reorder Points and Low-Stock Alerts
You get exact columns, copy-ready reorder-point formulas, a status column and conditional formatting that highlights items to reorder now or this week.
Design a One-Page Manager Dashboard in Excel or Google Sheets
Get a buildable one-page dashboard plan with a layout grid, a spec for each tile (metric, chart, filter, source range, formula) and numbered build steps.
Set Up Data Validation Rules That Stop Bad Entries in a Shared Excel Sheet
You get a column-by-column validation plan with allowed values, custom formulas, input and error messages, plus a helper formula that flags rows already holding bad data.