Part of the free Module 13: Excel Formulas with AI · Lesson 9 of 9 · Full Excel course
Updated on 5 September 2026 · Every function in this lesson (DATEDIF, TODAY, NETWORKDAYS, WORKDAY, EOMONTH, DATE, TEXT, MOD) works in Excel 365, 2021, 2019 and 2016. Any AI chat tool or Copilot can write the formulas. Results shown assume TODAY() is 5 September 2026.
Getting a correct date formula from AI depends on four facts the model cannot guess: whether your dates are real Excel dates or text, which day format your region uses, whether weekends and holidays count, and what today’s date is when you test. State those in the prompt and ChatGPT, Claude, Gemini or Copilot will produce working age, deadline, working-day and month-end formulas on the first attempt.
Why AI gets date formulas wrong more often than lookups
A date in Excel is a number: 5 September 2026 is 46270, and 12:00 noon is 0.5. Models know this, but they cannot see whether your column holds those numbers or the text “05/09/2026” exported from another system. They also cannot know whether 05/09 means 5 September or 9 May. The third trap is the calendar itself: “days until the deadline” might mean calendar days or working days, and “next month” might mean 30 days or the same day next month.
Every prompt in this lesson therefore adds one line to the five-part template from the prompt lesson: the date column holds real Excel dates in dd/mm/yyyy display format, or the date column is text in the form dd/mm/yyyy. That single line removes most wrong answers.
Which date function to ask for
| You want | Ask AI for | Version | Note |
|---|---|---|---|
| Age or years of service | DATEDIF with the y unit | All | Undocumented in the function list but works everywhere; use TODAY() as the end date |
| Days until or since a date | Simple subtraction, then IF for status | All | Format the result as a number, not a date |
| Working days between two dates | NETWORKDAYS or NETWORKDAYS.INTL | All (INTL from 2010) | Pass a holiday range as the third argument |
| A date N working days ahead | WORKDAY or WORKDAY.INTL | All | Same holiday range |
| Month end, quarter end | EOMONTH | All | EOMONTH(date,0) is this month; 2 is two months ahead |
| Same day next month or year | EDATE | All | 31 January plus one month returns 28 or 29 February |
| Fiscal year or quarter | MONTH with MOD or CHOOSE | All | Tell AI the month your fiscal year starts |
| Text to a real date | DATE with LEFT, MID, RIGHT | All | Safer than DATEVALUE, which depends on regional settings |
| Hours between two times | MOD for overnight shifts | All | Multiply by 24 for decimal hours |
Sample data used in this lesson
A Staff sheet holds A: Name, B: Start date, C: Birth date, all real Excel dates. A Holidays range in F2:F3 lists 7 September 2026 and 2 October 2026. Prompts name the exact cells so the formulas line up.
| Name (A) | Start date (B) | Birth date (C) |
|---|---|---|
| Asha | 14-Mar-2018 | 14-Mar-1990 |
| Ben | 01-Sep-2026 | 20-Nov-1995 |
| Chen | 10-Oct-2021 | 02-Feb-2001 |
Prompt 1: age in whole years
Goal: show each person's age in completed years as of today.
Data: sheet Staff, A1:C4, headers Name, Start date, Birth date; data in rows 2 to 4. Birth date is a real Excel date shown as dd-mmm-yyyy.
Rules: whole years only, no decimals; count a birthday on today's date as completed.
Excel 2019, English (UK).
Give the formula only for D2, then one sentence explaining it.=DATEDIF(C2,TODAY(),"y")Results on 5 September 2026: Asha 36, Ben 30 (his birthday in November has not arrived), Chen 25. A model that answers =YEAR(TODAY())-YEAR(C2) is wrong: it gives Ben 31. DATEDIF is missing from Excel’s function list and from IntelliSense, so AI sometimes calls it unreliable; it works in every version and is the right tool for ages and years of service. Ask the same prompt with Start date to get length of service.
Prompt 2: days to a deadline with a status flag
Column B on a Tasks sheet holds due dates. You want the number of days left in C and a status in D.
Goal: in C2 show days until the due date in B2 (negative if it has passed). In D2 show Overdue if the date has passed, Due soon if it is within 7 days, otherwise OK.
Data: sheet Tasks, A1:B5, headers Task, Due date; data in rows 2 to 5; Due date is a real Excel date.
Rules: today counts as 0 days left and is not overdue.
Excel 2016, English (UK).
Give both formulas, one line each, then one sentence.=B2-TODAY()
=IF(B2<TODAY(),"Overdue",IF(B2-TODAY()<=7,"Due soon","OK"))| Task | Due date | Days left | Status |
|---|---|---|---|
| Send invoice | 01-Sep-2026 | -4 | Overdue |
| Board pack | 10-Sep-2026 | 5 | Due soon |
| Audit file | 30-Sep-2026 | 25 | OK |
| Renew licence | 05-Sep-2026 | 0 | Due soon |
If column C shows a date such as 00-Jan-1900 instead of a number, Excel inherited the date format from B. Press Ctrl+Shift+~ on the cell for General format. Ask AI to add IF(B2="","",...) if some tasks have no due date yet; otherwise a blank behaves like 0 January 1900 and shows as Overdue.
Prompt 3: working days between two dates with holidays
Goal: count working days from the start date in B2 to the end date in C2, inclusive of both.
Data: sheet Projects, B2 and C2 are real Excel dates. Public holidays are listed in Holidays!F2:F3.
Rules: exclude Saturdays, Sundays and the listed holidays.
Excel 2019, English (UK).
Give the formula only for D2, then one sentence.=NETWORKDAYS(B2,C2,Holidays!$F$2:$F$3)For B2 = 1 September 2026 (a Tuesday) and C2 = 15 September 2026 the plain weekday count is 11; with 7 September in the holiday list the result is 10. If your weekend is Friday and Saturday, or Sunday only, say so and the correct answer switches to =NETWORKDAYS.INTL(B2,C2,7,Holidays!$F$2:$F$3) for a Friday-Saturday weekend or 11 as the weekend code for Sunday only. AI sometimes adds a fourth argument to plain NETWORKDAYS; it does not exist.
Prompt 4: a due date a set number of working days ahead
Goal: return the date that is the number of working days in C2 after the start date in B2.
Data: B2 is a real Excel date; C2 is a whole number of working days; holidays are in Holidays!F2:F3.
Rules: skip Saturdays, Sundays and holidays; the start date itself does not count as day 1.
Excel 2019, English (UK). Formula only for D2, then one sentence.=WORKDAY(B2,C2,Holidays!$F$2:$F$3)With B2 = 1 September 2026 and C2 = 10 the result is 16 September 2026, a Wednesday, because 7 September is skipped. Without the holiday list it would be 15 September. Format D2 as a date; WORKDAY returns the serial number 46281 in a General cell and people assume the formula is broken.
Prompt 5: month end, fiscal quarter and fiscal year
Goal: from the invoice date in B2 return three things: the last day of that month in C2, the fiscal quarter as Q1 to Q4 in D2, and the fiscal year as a label like 2026-27 in E2.
Data: B2 is a real Excel date. The fiscal year starts on 1 April.
Excel 2016, English (UK). Give the three formulas, one per line.=EOMONTH(B2,0)
="Q"&INT(MOD(MONTH(B2)-4,12)/3)+1
=IF(MONTH(B2)>=4,YEAR(B2),YEAR(B2)-1)&"-"&RIGHT(IF(MONTH(B2)>=4,YEAR(B2),YEAR(B2)-1)+1,2)| Invoice date | Month end | Fiscal quarter | Fiscal year |
|---|---|---|---|
| 05-Sep-2026 | 30-Sep-2026 | Q2 | 2026-27 |
| 15-Jan-2027 | 31-Jan-2027 | Q4 | 2026-27 |
| 01-Apr-2027 | 30-Apr-2027 | Q1 | 2027-28 |
The most common AI mistake here is ="Q"&ROUNDUP(MONTH(B2)/3,0), which is the calendar quarter. It returns Q3 for September. If you did not say “fiscal year starts on 1 April”, that answer is what you will get, and it is correct for the question the model thought you asked.
Prompt 6: turn text dates into real dates
Goal: convert the text in B2, which looks like 23/11/2026 and means day/month/year, into a real Excel date in C2.
Data: B2 is text, always ten characters, dd/mm/yyyy.
Rules: must work on a computer with US regional settings, so do not rely on DATEVALUE.
Excel 2016. Formula only, then one sentence.=DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2))“23/11/2026” becomes 23 November 2026 and “05/09/2026” becomes 5 September 2026 on any machine. =DATEVALUE(B2) is shorter and AI prefers it, but on a US-locale PC it reads 05/09/2026 as 9 May and returns #VALUE! for 23/11/2026. If the text is ISO format such as 2026-11-23, DATEVALUE is safe everywhere. Once converted, copy the column and paste as values, or keep the helper column and hide it.
Prompt 7: hours worked across midnight
Goal: hours worked as a decimal from the clock-in time in B2 to the clock-out time in C2. Shifts can cross midnight.
Data: B2 and C2 are real Excel times without dates, shown as hh:mm.
Excel 2016. Formula only for D2, then one sentence.=MOD(C2-B2,1)*2409:00 to 17:30 gives 8.5; 22:00 to 06:00 gives 8. Plain =(C2-B2)*24 returns -16 for the night shift, and Excel shows a time result that goes negative as a row of hash marks. Format D2 as a number with one decimal, not as a time.
Worked example: a project tracker with holidays
A Projects sheet has Task in A, Start date in B and Duration in working days in C. You need the end date in D, working days remaining in E and a status in F. Holidays!F2:F3 holds 7 September and 2 October 2026. Today is 5 September 2026.
- Paste the three data rows and the holiday list into the chat, then use Prompt 4 for D2:
=WORKDAY(B2,C2,Holidays!$F$2:$F$3). Copy down. - Follow up: “In E2 count working days from today to the end date in D2, inclusive of both, using the same holidays. Return 0 if the end date has passed.” Expect
=MAX(0,NETWORKDAYS(TODAY(),D2,Holidays!$F$2:$F$3)). - Follow up: “In F2 show Late if D2 is before today, At risk if E2 is 5 or less, otherwise On track.” Expect
=IF(D2<TODAY(),"Late",IF(E2<=5,"At risk","On track")). - Check the results against the table below by counting on a calendar, then format D as a date and E as a number.
| Task | Start | Duration | End date | Days remaining | Status |
|---|---|---|---|---|---|
| Design | 01-Sep-2026 | 10 | 16-Sep-2026 | 7 | On track |
| Build | 08-Sep-2026 | 5 | 15-Sep-2026 | 6 | On track |
| Test | 14-Sep-2026 | 8 | 24-Sep-2026 | 13 | On track |
Design ends on 16 September because the holiday on 7 September pushes it one day. Days remaining for Design counts 8 to 16 September, eight weekdays, minus the holiday, giving 7. Change the Build start to 25 August and its status becomes At risk, then Late once the end date passes.
Tips and common mistakes
- Say whether dates are real or text. Test with
=ISNUMBER(B2); FALSE means text and every date function will fail or lie. - State your day and month order. dd/mm or mm/dd changes both the formula and the test result.
- Give the holiday range explicitly. AI cannot know where your holidays live and will otherwise leave the argument out.
- Define “days” every time. Calendar days, working days or business days with a custom weekend are three different formulas.
- Freeze TODAY() when you test. Put a fixed date in a cell and reference it, so your expected results do not shift tomorrow.
- Format the answer cell yourself. Date maths often returns a serial number or a date where a number was wanted; the formula is usually fine.
- Ask for EDATE, not +30. “One month later” means EDATE(B2,1); adding 30 days drifts after February.
Errors and how to fix them
| Symptom | Likely cause | Fix |
|---|---|---|
| #VALUE! from a date function | The date is text, or dd/mm was read as mm/dd | Convert with DATE(RIGHT, MID, LEFT) or Text to Columns; check with ISNUMBER |
| #NUM! from DATEDIF | Start date is later than end date | Swap the arguments or wrap in IF(C2>TODAY(),””,…) |
| A row of hash marks | Negative time in a time-formatted cell, or a narrow column | Use MOD for overnight times; widen the column; use a number format |
| Result shows 15-Sep-2026 when you wanted 10 | Result cell inherited a date format | Apply General or Number format |
| Result shows 46281 when you wanted a date | WORKDAY or EOMONTH result in a General cell | Apply a date format |
| #NAME? on NETWORKDAYS.INTL | Excel 2007 or older, or a typo such as NETWORKDAY | Correct the spelling; use NETWORKDAYS if INTL is unavailable |
| Quarter is off by two | Calendar quarter instead of fiscal quarter | Tell AI the fiscal year start month and re-ask |
| Dates four years and one day out | Workbook uses the 1904 date system (common in files from Mac) | File > Options > Advanced > Use 1904 date system, untick, and re-enter the dates |
Practice exercise
- Enter the Staff table and ask AI for years of service in whole years and months, for example “8 years 5 months”. Check Asha by hand.
- Ask for a formula that returns the last working day of the month for the date in B2, honouring your holiday list. Test it with a month that ends on a Sunday.
- Type five due dates as text in dd/mm/yyyy form. Ask for a conversion formula that works on US and UK machines, and confirm ISNUMBER returns TRUE afterwards.
- Ask for a fiscal quarter formula with a fiscal year starting 1 July. Check January, June and July.
- Build a timesheet with clock-in and clock-out times, including one night shift, and ask for total weekly hours in decimal form.
Key takeaways
- Tell AI whether your dates are real Excel dates or text, and which day-month order they use.
- DATEDIF for ages and service, NETWORKDAYS and WORKDAY for working days, EOMONTH and EDATE for month maths: all work in every Excel version.
- Pass the holiday range explicitly and define what “days” means before asking.
- Fiscal periods need the start month in the prompt or you get calendar quarters.
- Most “wrong” date results are formatting: check ISNUMBER and apply the right number or date format before blaming the formula.
Related lessons
- Excel Formulas with AI: course hub
- How to Write a Prompt for an Excel Formula: The 5-Part Template
- IF, IFS, SWITCH, AND and OR Formulas Written with AI
- Verifying AI Formulas: Test Cases, Edge Cases and Hallucinations
- DATEDIF, NETWORKDAYS, WORKDAY, EOMONTH, EDATE and TODAY in the formulas library
- Microsoft Support: Date and time functions reference
Frequently asked questions
Can ChatGPT calculate age from a date of birth in Excel?
Yes. Ask for completed years from the birth date to TODAY() and it should return DATEDIF(C2,TODAY(),”y”). If it returns YEAR(TODAY())-YEAR(C2), reply that the answer must not count a birthday that has not happened yet. DATEDIF works in every Excel version even though it is missing from the function list.
How do I ask AI for working days excluding holidays?
Say “working days, excluding Saturdays, Sundays and the holidays listed in F2:F3”, with the exact range. The formula is NETWORKDAYS(start,end,holidays) for a count and WORKDAY(start,days,holidays) for a future date. Mention a non-standard weekend and AI should switch to the INTL versions with a weekend code.
Why does my AI date formula return #VALUE!?
Almost always because the cell holds text that looks like a date, or because the day and month are in the opposite order from your regional settings. Check with ISNUMBER; if it is FALSE, convert the text with DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2)) or Text to Columns before using date functions.
What is the difference between EDATE and EOMONTH?
EDATE(date,n) returns the same day of the month n months away, so 15 January plus 1 is 15 February. EOMONTH(date,n) returns the last day of the month n months away, so 15 January with 0 is 31 January and with 1 is 28 or 29 February. Use EDATE for anniversaries and EOMONTH for period ends.
Does AI know my fiscal year?
No. Unless you state the month the fiscal year starts, every model assumes a calendar year and returns calendar quarters. Add “fiscal year starts 1 April” to the prompt and ask it to show the results for a January date and an April date so you can confirm the boundary is right.
Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.