How to Use Date Functions in Excel (With Practical Examples)
Introduction
Dates show up in almost every spreadsheet. Deadlines, hire dates, invoice due dates, project timelines. Excel has dedicated functions for calculating differences between dates, shifting dates by months, and counting business days. Here's how to use them.
How Excel Handles Dates
First, a quick look at how Excel stores dates. Every date is a serial number, a simple integer where January 1, 1900, equals 1. Each day after that adds 1 to the count.
| Date | Serial Number |
|---|---|
| January 1, 1900 | 1 |
| January 1, 2000 | 36,526 |
| January 1, 2026 | 46,023 |
| February 27, 2026 | 46,080 |
This is why you can subtract one date from another and get a number of days. It's just arithmetic on integers. When you format a cell as a date, Excel translates the serial number into something readable, but the math always runs on the number underneath.
Key takeaway: Dates in Excel are just numbers. Every date function is built on this system, which is why date arithmetic works so reliably.
TODAY and NOW
The most common starting point is getting the current date into your spreadsheet.
=TODAY()
TODAY returns the current date with no time component. It updates automatically every time the workbook recalculates, so formulas that reference it always stay current.
=NOW()
NOW works the same way but includes the current time as a decimal portion of the serial number. If you only care about the date, stick with TODAY.
| Formula | Returns | Updates |
|---|---|---|
=TODAY() |
Current date (no time) | On every recalculation |
=NOW() |
Current date and time | On every recalculation |
=TODAY() - A2 |
Days since the date in A2 | Automatically |
A practical use: calculating how many days remain until a deadline.
=A2 - TODAY()
If A2 contains a future date, this returns the number of days remaining. If the date has passed, you get a negative number.
YEAR, MONTH, and DAY
Sometimes you need to pull a specific component out of a date. That's where YEAR, MONTH, and DAY come in.
=YEAR(A2)
=MONTH(A2)
=DAY(A2)
Each function takes a date and returns a single number.
| Date in A2 | Formula | Result |
|---|---|---|
| March 15, 2026 | =YEAR(A2) |
2026 |
| March 15, 2026 | =MONTH(A2) |
3 |
| March 15, 2026 | =DAY(A2) |
15 |
You can use these to group data by year or month. For example, if you have a list of transactions and want to sum by month, use MONTH to extract the month number and pair it with SUMIFS.
=SUMIFS(C2:C100, MONTH(A2:A100), 3)
This sums all values in column C where the month in column A is March.
DATE
The DATE function works in the opposite direction. Instead of breaking a date apart, it assembles one from separate year, month, and day values.
=DATE(year, month, day)
| Formula | Result |
|---|---|
=DATE(2026, 6, 15) |
June 15, 2026 |
=DATE(2026, 1, 1) |
January 1, 2026 |
=DATE(YEAR(A2), MONTH(A2), 1) |
First day of A2's month |
That last example is a common trick. By plugging YEAR and MONTH from an existing date into DATE with 1 as the day, you always get the first of that month. This is handy for monthly reporting and period comparisons.
DATE also handles overflow. If you pass month 13, Excel rolls it into the next year.
| Formula | Result |
|---|---|
=DATE(2026, 13, 1) |
January 1, 2027 |
=DATE(2026, 0, 1) |
December 1, 2025 |
=DATE(2026, 3, 0) |
February 28, 2026 |
Unlike typing a date string, DATE always produces a proper serial number regardless of your locale or date format settings.
DATEDIF
DATEDIF calculates the difference between two dates in years, months, or days. It doesn't appear in Excel's autocomplete, and you won't find it in the function wizard, but it works and has been available since Excel 2000.
=DATEDIF(start_date, end_date, unit)
The unit argument tells DATEDIF what to measure.
| Unit | Meaning | Example |
|---|---|---|
"Y" |
Complete years between dates | Age in years |
"M" |
Complete months between dates | Tenure in months |
"D" |
Total days between dates | Same as subtraction |
"YM" |
Months remaining after complete years | The "months" part of age |
"MD" |
Days remaining after complete months | The "days" part of age |
"YD" |
Days remaining after complete years | Day-of-year difference |
Suppose A2 contains a hire date of March 10, 2021, and today is February 27, 2026.
| Formula | Result | Meaning |
|---|---|---|
=DATEDIF(A2, TODAY(), "Y") |
4 | 4 complete years |
=DATEDIF(A2, TODAY(), "M") |
59 | 59 complete months |
=DATEDIF(A2, TODAY(), "D") |
1,815 | 1,815 total days |
=DATEDIF(A2, TODAY(), "YM") |
11 | 11 months past the 4-year mark |
=DATEDIF(A2, TODAY(), "MD") |
17 | 17 days past the last complete month |
You can combine units to build a human-readable tenure string:
=DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months"
This would return something like 4 years, 11 months.
EDATE and EOMONTH
EDATE and EOMONTH shift a date forward or backward by a specific number of months.
=EDATE(start_date, months)
=EOMONTH(start_date, months)
EDATE returns the same day of the month, shifted by the number of months you specify. EOMONTH returns the last day of the target month instead.
| Start Date | Formula | Result |
|---|---|---|
| January 15, 2026 | =EDATE(A2, 3) |
April 15, 2026 |
| January 15, 2026 | =EDATE(A2, -2) |
November 15, 2025 |
| January 15, 2026 | =EOMONTH(A2, 0) |
January 31, 2026 |
| January 15, 2026 | =EOMONTH(A2, 1) |
February 28, 2026 |
| January 15, 2026 | =EOMONTH(A2, -1) |
December 31, 2025 |
Notice that EOMONTH(A2, 0) gives you the last day of the current month. This is a quick way to find month-end dates without worrying about whether the month has 28, 30, or 31 days.
Both come up frequently in finance for payment schedules, contract renewal dates, and reporting period boundaries.
NETWORKDAYS and WORKDAY
When you need to count or calculate with business days (excluding weekends), these two functions are essential.
=NETWORKDAYS(start_date, end_date)
=WORKDAY(start_date, days)
NETWORKDAYS counts the business days between two dates. WORKDAY returns the date that falls a certain number of business days from a start date.
| Scenario | Formula | Result |
|---|---|---|
| Business days in March 2026 | =NETWORKDAYS(DATE(2026,3,1), DATE(2026,3,31)) |
22 |
| 10 business days from Jan 5, 2026 | =WORKDAY(DATE(2026,1,5), 10) |
January 19, 2026 |
| Business days until deadline | =NETWORKDAYS(TODAY(), B2) |
Varies |
Both functions automatically skip Saturdays and Sundays. If you also need to exclude holidays, pass a range of holiday dates as a third argument.
=NETWORKDAYS(A2, B2, D2:D10)
Here, D2:D10 contains a list of company holidays. Any dates in that range are skipped along with weekends.
There are also NETWORKDAYS.INTL and WORKDAY.INTL variants that let you define custom weekend days. This is useful for regions where the weekend falls on Friday and Saturday instead of Saturday and Sunday.
| Weekend Schedule | Weekend Code |
|---|---|
| Saturday, Sunday | 1 (default) |
| Sunday, Monday | 2 |
| Friday, Saturday | 7 |
| Friday only | 11 |
| Saturday only | 12 |
| Sunday only | 13 |
=NETWORKDAYS.INTL(A2, B2, 7)
This counts business days assuming Friday and Saturday are the weekend.
Putting It Together: Employee Tracker
Say you have an employee roster and want to calculate tenure, upcoming review dates, and business days until the next review.
Employee Data:
| Name | Hire Date | Department |
|---|---|---|
| Sarah | June 12, 2019 | Marketing |
| James | November 3, 2021 | Engineering |
| Priya | March 20, 2024 | Finance |
| Marcus | August 8, 2025 | Operations |
Calculated Columns:
| Name | Tenure | Next Annual Review | Business Days Until Review |
|---|---|---|---|
| Sarah | 6 years, 8 months | June 12, 2026 | 74 |
| James | 4 years, 3 months | November 3, 2026 | 176 |
| Priya | 1 years, 11 months | March 20, 2026 | 15 |
| Marcus | 0 years, 6 months | August 8, 2026 | 115 |
The formulas behind each calculated column:
Tenure (combining DATEDIF with text concatenation):
=DATEDIF(B2, TODAY(), "Y") & " years, " & DATEDIF(B2, TODAY(), "YM") & " months"
Next Annual Review (using DATE and IF to find the next anniversary):
=DATE(YEAR(TODAY()) + IF(DATE(YEAR(TODAY()), MONTH(B2), DAY(B2)) < TODAY(), 1, 0), MONTH(B2), DAY(B2))
This formula checks whether this year's anniversary has already passed. If it has, it returns next year's date.
Business Days Until Review (using NETWORKDAYS):
=NETWORKDAYS(TODAY(), C2)
Where C2 is the "Next Annual Review" column.
Key takeaway: A few date formulas can turn a static employee list into a tracker that updates itself every day.
Quick Reference
Here's a summary of every function covered in this guide.
| Function | Purpose | Syntax |
|---|---|---|
| TODAY | Current date | =TODAY() |
| NOW | Current date and time | =NOW() |
| YEAR | Extract year from date | =YEAR(date) |
| MONTH | Extract month from date | =MONTH(date) |
| DAY | Extract day from date | =DAY(date) |
| DATE | Build date from parts | =DATE(year, month, day) |
| DATEDIF | Difference between dates | =DATEDIF(start, end, unit) |
| EDATE | Shift date by months | =EDATE(date, months) |
| EOMONTH | End of month shifted by months | =EOMONTH(date, months) |
| NETWORKDAYS | Count business days | =NETWORKDAYS(start, end) |
| WORKDAY | Date after N business days | =WORKDAY(date, days) |
| NETWORKDAYS.INTL | Business days with custom weekends | =NETWORKDAYS.INTL(start, end, weekend) |
| WORKDAY.INTL | Workday with custom weekends | =WORKDAY.INTL(date, days, weekend) |
Conclusion
TODAY and DATEDIF cover most date calculation needs. Add EDATE, EOMONTH, and NETWORKDAYS when you're working with month-end reporting or business day schedules, and you won't need to hardcode another date again.
For related functions, check out our guide on PMT and loan payment calculations or learn how to handle formula errors with IFERROR.
Level up your Excel skills
Bite-sized lessons, drills, and daily challenges to build real spreadsheet skills.