Excel Date Functions: TODAY, DATEDIF, NETWORKDAYS, EOMONTH
Date calculations in Excel are simpler than they look — dates are just numbers. Once you understand that, these functions become straightforward tools for calculating deadlines, ages, durations, and working days.
The Key Fact
Excel stores every date as a number (the count of days since 1 January 1900). So 14/08/2026 is stored as 46,277. That means you can do arithmetic directly: =B2-A2 gives the number of days between two dates. Format the result as a number, not a date.
Today and now
TODAY()=TODAY()Returns today's date. Recalculates automatically every time the workbook opens. The result is a real Excel date serial number — you can do maths with it.
Formula
=TODAY()Returns
Current date (e.g. 14/08/2026)NOW()=NOW()Returns the current date and time. Recalculates every time the workbook recalculates. For a static timestamp, use Ctrl+; (date) or Ctrl+Shift+: (time).
Formula
=NOW()Returns
14/08/2026 14:30Build a date
DATE=DATE(year, month, day)Constructs a date from separate year, month, and day values. Essential for building dynamic dates from cell references or formulas.
Formula
=DATE(2026, 12, 31)Returns
31/12/2026DATEVALUE=DATEVALUE(date_text)Converts a date stored as text into a real Excel date number. Use when dates are imported as text strings.
Formula
=DATEVALUE("14 Aug 2026")Returns
Real date serial number (46,277)Extract parts of a date
YEAR=YEAR(date)Returns the year portion of a date as a 4-digit number.
Formula
=YEAR(TODAY())Returns
2026MONTH=MONTH(date)Returns the month as a number (1–12).
Formula
=MONTH(TODAY())Returns
8 (for August)DAY=DAY(date)Returns the day of the month (1–31).
Formula
=DAY(TODAY())Returns
14WEEKDAY=WEEKDAY(date, [return_type])Returns the day of the week as a number. return_type 2 gives Mon=1, Tue=2...Sun=7 (most useful for European weeks).
Formula
=WEEKDAY(TODAY(), 2)Returns
4 (for Thursday)WEEKNUM=WEEKNUM(date, [return_type])Returns the week number of the year (1–53).
Formula
=WEEKNUM(TODAY())Returns
33 (week 33 of 2026)Calculate differences
DATEDIF=DATEDIF(start_date, end_date, unit)Calculates the difference between two dates in "Y" (years), "M" (months), or "D" (days). Hidden from autocomplete but fully functional.
Formula
=DATEDIF(A2, TODAY(), "Y")Returns
Number of complete years (e.g., age)DAYS=DAYS(end_date, start_date)Returns the number of days between two dates. Equivalent to =end_date - start_date but with clearer syntax.
Formula
=DAYS(B2, A2)Returns
Number of days between A2 and B2NETWORKDAYS=NETWORKDAYS(start, end, [holidays])Counts business days (Mon–Fri) between two dates. Pass a list of holiday dates as the third argument to exclude them.
Formula
=NETWORKDAYS(A2, B2, HolidayList)Returns
Working days excluding weekends and holidaysNETWORKDAYS.INTL=NETWORKDAYS.INTL(start, end, [weekend], [holidays])Like NETWORKDAYS but lets you define which days are weekends. "11" = Sunday only; "0000001" = Saturday only; a custom string for any pattern.
Formula
=NETWORKDAYS.INTL(A2, B2, "0000011")Returns
Working days with Sat+Sun as weekendCalculate future/past dates
EDATE=EDATE(start_date, months)Returns a date that is exactly N months before or after the start date. Positive = future, negative = past.
Formula
=EDATE(TODAY(), 3)Returns
Date 3 months from todayEOMONTH=EOMONTH(start_date, months)Returns the last day of the month, N months from the start date. EOMONTH(A2, 0) = last day of A2's month.
Formula
=EOMONTH(TODAY(), 0)Returns
31/08/2026 (last day of August)WORKDAY=WORKDAY(start_date, days, [holidays])Returns the date that is N working days from the start date. Useful for calculating deadlines.
Formula
=WORKDAY(TODAY(), 10)Returns
Date 10 working days from today10 Useful Date Patterns
Copy-paste formulas for the most common date calculation tasks.
| Task | Formula |
|---|---|
| Age in years from date of birth | =DATEDIF(A2, TODAY(), "Y") |
| Days until a deadline | =B2-TODAY() |
| Days overdue (0 if not yet due) | =MAX(0, TODAY()-B2) |
| First day of current month | =DATE(YEAR(TODAY()), MONTH(TODAY()), 1) |
| Last day of current month | =EOMONTH(TODAY(), 0) |
| First day of next month | =EOMONTH(TODAY(), 0)+1 |
| Same day last year | =EDATE(TODAY(), -12) |
| Working days in current month | =NETWORKDAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),1), EOMONTH(TODAY(),0)) |
| Quarter number from a date | =ROUNDUP(MONTH(A2)/3, 0) |
| Week start (Monday) of a date | =A2-WEEKDAY(A2, 2)+1 |
Why Are My Dates Showing as Numbers?
If a date formula returns a large number like 46,277 instead of a date, the cell is formatted as General or Number. Select the cell → Ctrl+1 (Format Cells) → Number tab → Date → choose your preferred format. The value is correct; it just needs a date display format.
Dates Stored as Text
Dates imported from CSV files, ERP exports, or web scrapes are often stored as text strings ("14/08/2026" as text, not a real date). You cannot do maths on text dates. To convert them:
- Use DATEVALUE:
=DATEVALUE("14/08/2026") - Or use Text to Columns on the column → Date format → Finish
- Or use Power Query: Change the column type to Date in the editor
If your dates are in an ambiguous format like "08/14/2026" (US MM/DD/YYYY) but your locale expects DD/MM/YYYY, Excel may misinterpret them. Use DATEVALUE with explicit format or Power Query's locale-aware type conversion.
DATEDIF Gotchas
DATEDIF has one known bug: when the start date is near month-end and the end date is the last day of a later month, the "YM" and "MD" units can return incorrect results. For most use cases (age in years, months elapsed, days remaining), the standard units "Y", "M", and "D" are reliable. If you need precise month-boundary calculations, consider using EDATE and subtraction instead.
Frequently Asked Questions
How do I calculate the number of days between two dates in Excel?
Subtract the earlier date from the later date: =B2-A2. Format the result as Number. For months: =DATEDIF(A2,B2,"M"). For complete years: =DATEDIF(A2,B2,"Y").
What does DATEDIF do in Excel and why is it hidden?
DATEDIF calculates the difference between two dates in days, months, or years. It is hidden from autocomplete because it was inherited from Lotus 1-2-3 and has known edge-case bugs, but it still works in all Excel versions.
How do I calculate working days in Excel?
=NETWORKDAYS(start, end) counts Mon–Fri days. Add a holiday list as the third argument to exclude public holidays. NETWORKDAYS.INTL lets you customise which days count as weekends.
Related Guides
Date Format Not Working
Dates showing as numbers or the wrong value — every cause and fix explained.
Read →
Excel Text Functions
TEXTBEFORE, TEXTAFTER, MID — extract and clean text fields that contain dates.
Read →
Conditional Formatting
Highlight overdue dates, upcoming deadlines, and date ranges automatically.
Read →
Excel Formulas Hub
Date functions alongside lookup, text, and statistical formula categories.
Read →