Create intermediate Works with: Any AI

Date Calculation Formulas That Handle Real Cases

Table output Has example

Compute ages, deadlines, and durations correctly.

The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.

Write date formulas for my spreadsheet. App: [SPREADSHEET_APP]. Tasks: [DATE_TASKS]. Local workweek and holidays: [WORKWEEK_AND_HOLIDAYS]. Sample dates (fake): [SAMPLE_DATES].

For each task provide a table row: Task | Formula | Explanation | Example result | Edge cases.
Cover: days between dates; working days between dates excluding weekends and a holiday list (NETWORKDAYS, NETWORKDAYS.INTL for different weekends); due date after N working days (WORKDAY); age in years, months, and days (DATEDIF and its quirks); months between dates; end of month (EOMONTH) and first day of next month; adding months without overflow; fiscal year and quarter from a date; week number and the definition used (ISO or not); next occurrence of a weekday; converting text dates; combining a date and a time; time differences across midnight; and time zone adjustments.

Add rules: store dates as real dates, not text; use cell references for holidays; test leap days (29 February), month ends (31 January plus one month), and year boundaries. Provide a test table with at least eight cases and expected results, and warn about regional date format differences when importing.

Fill in before use

Optional: type your details and the prompt above updates. Nothing is saved or sent.

Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.

Task | Formula | Example
Working days between | =NETWORKDAYS(A2, B2, Holidays) | 1 Oct to 15 Oct 2026 with one holiday = 10
Due date after 10 working days | =WORKDAY(A2, 10, Holidays) | 1 Oct -> 15 Oct
Age in years | =DATEDIF(A2, TODAY(), "Y") | quirks with DATEDIF documented
End of month | =EOMONTH(A2, 0) | 31 Oct
Add a month without overflow | =EDATE(A2, 1) | 31 Jan -> 28 Feb
Edge cases to test: 29 Feb, month ends, year boundaries. Store real dates; keep holidays in a named range.

• dates