Tutorials›Excel›Date Functions
🟢 Free Demo
Excel TutorialTopic 8 of 31

Date Functions

Work with dates in Excel — calculate durations, extract components, and build date-driven reports.

✅ What You Will Learn

›How Excel stores dates internally as serial numbers
›How to get today date and current time with TODAY and NOW
›How to extract year, month, day, and weekday from a date
›How to calculate the difference between two dates with DATEDIF and DAYS
›How to add or subtract months and years with EDATE and EOMONTH

Excel stores every date as a serial number — January 1, 1900 is 1, January 2 is 2, and so on. This means you can do arithmetic on dates: subtracting two dates gives the number of days between them, and adding a number to a date gives a future date.

Date functions extract components from this serial number (year, month, day) or calculate intervals. The most important date functions for analysts are TODAY, NOW, YEAR, MONTH, DAY, WEEKDAY, DATEDIF, EDATE, and EOMONTH.

Dates imported from external systems are often stored as text — they look like dates but do not behave like them. You will know a date is stored as text if it is left-aligned in the cell (real dates are right-aligned by default) or if subtracting two dates gives an error.

📋 Employee records — date calculations

EmployeeJoin DateDate of BirthTodayTenure (Years)Age
Rahul Sharma15-Mar-202112-Jun-1995=TODAY()=DATEDIF formula=DATEDIF formula
Anjali Singh01-Aug-201930-Sep-1992
Vikas Gupta20-Nov-202315-Jan-1998

Syntax

EXCEL SYNTAX
=TODAY()               → today's date (no arguments)
=NOW()                 → current date and time
=YEAR(date)            → extracts year (e.g. 2026)
=MONTH(date)           → extracts month number (1–12)
=DAY(date)             → extracts day of month
=WEEKDAY(date, 2)      → day of week (2 = Mon=1 to Sun=7)
=DATEDIF(start, end, "D")   → days between dates
=DATEDIF(start, end, "M")   → complete months between dates
=DATEDIF(start, end, "Y")   → complete years between dates
=EDATE(date, months)   → date N months in the future/past
=EOMONTH(date, 0)      → last day of the same month
=NETWORKDAYS(start, end, [holidays]) → working days between dates

Examples

Example 1Calculate employee tenure in complete years
=DATEDIF(B2, TODAY(), "Y")
OUTPUT
Rahul (joined Mar 2021): 5 years  (as of Aug 2026)
💡

DATEDIF is a hidden function — it does not appear in the autocomplete list but it works. "Y" = complete years, "M" = complete months, "D" = days. Always put the earlier date first.

Example 2Extract month name from a date
=TEXT(B2, "MMMM")
OUTPUT
"March"
💡

TEXT converts a date to a formatted string. Use "MMM" for short month names (Jan, Feb) and "MMMM" for full names. This is useful for grouping data by month name in reports.

Example 3Find the last day of any month with EOMONTH
=EOMONTH(TODAY(), 0)     → last day of current month
=EOMONTH(TODAY(), 1)     → last day of next month
=EOMONTH(TODAY(), -1)    → last day of previous month
OUTPUT
=EOMONTH(DATE(2026,8,1), 0) → 31-Aug-2026
💡

EOMONTH with 0 months always returns the last calendar day of the date given month — useful for building month-end reporting deadlines.

📌 Key Points to Remember

  • ✓Dates are numbers internally — subtracting two dates gives days; adding a number gives a future date
  • ✓TODAY() recalculates automatically every time the workbook opens or calculates
  • ✓DATEDIF is not in autocomplete — type it manually. Arguments: (start_date, end_date, unit)
  • ✓If date subtraction gives a number like 47823 instead of days, format the result cell as "Number" not "Date"
  • ✓NETWORKDAYS excludes weekends and optionally a list of holidays — use it for SLA and deadline calculations

🏢 Real-World Application

Operations teams use NETWORKDAYS to calculate SLA breaches — how many working days have elapsed since a ticket was raised. HR uses DATEDIF(DOB, TODAY(), "Y") to calculate employee ages for compliance reporting. Finance teams use EOMONTH to generate month-end cut-off dates for automated reporting triggers.

⚠️ Common Mistakes to Avoid

WRONGPutting the end date before the start date in DATEDIF
FIXDATEDIF(start, end, unit) — start must be the earlier date. If end < start, DATEDIF returns #NUM!. Wrap in IFERROR if dates can be in either order.
WRONGDates showing as numbers (e.g. 46693) after date arithmetic
FIXThe cell is formatted as "General" or "Number". Select the cell, press Ctrl+1, choose "Date" format, and pick the display format you want.
WRONGUsing YEAR(B2)-YEAR(A2) for age/tenure — gives the wrong answer near anniversaries
FIXUse DATEDIF(A2, B2, "Y") for complete years. YEAR subtraction gives 2026-2021=5 even if the anniversary has not passed yet this year.
✏️Test Yourself

What does =DATEDIF("01-Jan-2020", "20-Aug-2026", "Y") return?

❓ Frequently Asked Questions

How do I convert a text date like "15-08-2026" to a real Excel date?

Use DATEVALUE("15-08-2026") or =DATE(YEAR,MONTH,DAY) built from MID/LEFT/RIGHT extractions. If dates are consistently formatted, Flash Fill (Ctrl+E) often converts them automatically.

What is the difference between TODAY() and NOW()?

TODAY() returns only the date (no time). NOW() returns the date and time together. Use TODAY() for most date calculations — NOW() is mainly for timestamping when an event was recorded.

How do I count working days between two dates excluding public holidays?

=NETWORKDAYS(start, end, holiday_range) where holiday_range is a list of holiday dates in a column. NETWORKDAYS automatically excludes Saturdays and Sundays plus any dates you list.

✏️ Practice Exercise

Create an employee data table with Name, Join Date (dates in the past), and Date of Birth columns. Using date functions, calculate: (1) Tenure in complete years and months (e.g. "5 years, 3 months"), (2) Current age in years, (3) The exact date their 5-year work anniversary falls on, (4) Number of working days they have been employed.

← PreviousText FunctionsNext →Pivot Tables
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.