Excel date functions: calculate and analyze dates
Date functions work out due dates, split a date into year, month and day, or count working days. A typical case is the payment deadline 30 days after an invoice date.
In short: to Excel a date is a number. January 1, 1900 is 1, and each day adds one. That is why =B2+30 works and why =MONTH(B2) can pull the month out of a date.
Try it now
Column B holds invoice dates. Return the month number for each one, the first step of any monthly report.
Task In C2, return the month number of the invoice date. The formula is filled down to C6.
| A | B | C | |
|---|---|---|---|
| 1 | Invoice | Date | Month |
| 2 | R-1001 | 1/5/2026 | |
| 3 | R-1002 | 2/18/2026 | |
| 4 | R-1003 | 4/3/2026 | |
| 5 | R-1004 | 7/1/2026 | |
| 6 | R-1005 | 11/24/2026 |
Dates are serial numbers
Behind every date sits a running serial number. January 5, 2026 is 46027 and the next day is 46028. What you see in the cell is just the number format applied on top. Times are the decimal part, so 46027.5 means noon on January 5, 2026.
Everything else follows from that. You can add days, subtract one date from another and compare dates just like numbers. =C2-B2 returns the number of days between two dates.
Calculating with dates
A payment deadline needs no function at all. Invoice date plus 30 is the due date, and Excel handles month ends for you.
Task Invoices are due 30 days after the invoice date. In C2, calculate the due date. The formula is filled down to C6.
| A | B | C | |
|---|---|---|---|
| 1 | Invoice | Invoice date | Due date |
| 2 | R-2001 | 1/5/2026 | |
| 3 | R-2002 | 1/19/2026 | |
| 4 | R-2003 | 2/2/2026 | |
| 5 | R-2004 | 2/20/2026 | |
| 6 | R-2005 | 3/9/2026 |
Key date functions
| Function | What it does | Example with B2 = 1/5/2026 |
|---|---|---|
| DATE | Builds a date from year, month and day | =DATE(2026,1,5) |
| YEAR, MONTH, DAY | Extract the parts of a date | =YEAR(B2) returns 2026 |
| WEEKDAY | Day of the week as a number | =WEEKDAY(B2,2) returns 1 (Monday) |
| TODAY, NOW | Current date, NOW adds the time | =TODAY() |
| EDATE | Same day a number of months later or earlier | =EDATE(B2,3) returns 4/5/2026 |
| EOMONTH | Last day of a month | =EOMONTH(B2,0) returns 1/31/2026 |
| NETWORKDAYS | Working days, Monday to Friday, between two dates | =NETWORKDAYS(B2,C2,F2:F10) |
| ISOWEEKNUM | ISO week number, the standard in Europe | =ISOWEEKNUM(B2) returns 2 |
WEEKDAY on its own counts from Sunday = 1. With 2 as the second argument, Monday becomes 1 and Sunday 7, which is easier for business rules. NETWORKDAYS counts both the start and end date and takes an optional range of holidays as its third argument.
TODAY and NOW are volatile: they recalculate whenever anything in the workbook changes and every time you open it. For a fixed timestamp, such as the date a request came in, press Ctrl+; to type today's date as a static value.
Practice: build a date from its parts
Some systems export day, month and year as separate fields. DATE turns them back into a real date you can calculate with.
Task The HR system exports the day, month and year an employee joined as separate fields. In B6, turn them into a real date.
| A | B | |
|---|---|---|
| 1 | Export from the HR system | |
| 2 | Day | 15 |
| 3 | Month | 4 |
| 4 | Year | 2026 |
| 5 | ||
| 6 | Start date |
DATE is forgiving with overflow. =DATE(2026,13,1) returns January 1, 2027. That makes =DATE(YEAR(B2),MONTH(B2)+1,1) a reliable way to get the first day of the following month.
DATEDIF, the hidden function
For age, length of service or contract terms in whole years or months there is DATEDIF. In our experience it does not appear in the Insert Function dialog or in formula autocomplete, yet it works: =DATEDIF(B2,TODAY(),"Y") returns the complete years since the date in B2. Use "M" for complete months and "D" for days. The start date has to come first, otherwise you get #NUM!. All units, working days with holidays and week numbers are covered in DATEDIF and NETWORKDAYS.
Common problems with dates
The date shows as a number
If a cell shows 46057 instead of 2/4/2026, only the format is off. Select the cell, press Ctrl+1 and pick Date under Number. The reverse happens too: a difference like =C2-B2 may show up as 1/30/1900 because Excel copied the date format. Switch that cell to General.
The date is really text
Imported dates are often left-aligned and refuse to calculate, because Excel sees text instead of a serial number. Fix them with Data > Text to Columns using the Date column format, or with DATEVALUE. When the parts come in a fixed pattern, combine DATE with the text functions LEFT, MID and RIGHT.
Day and month swapped
A file created with European settings writes 05.01.2026 for January 5. Opened with US settings, text dates like that may be read as May 1 or not recognized at all. Real date values are safe, since only the display changes. Text dates need converting with Text to Columns, where you can choose the order DMY.
Date functions in German Excel
| English | German |
|---|---|
| DATE, YEAR, MONTH, DAY | DATUM, JAHR, MONAT, TAG |
| WEEKDAY, TODAY, NOW | WOCHENTAG, HEUTE, JETZT |
| EDATE, EOMONTH | EDATUM, MONATSENDE |
| NETWORKDAYS, WORKDAY | NETTOARBEITSTAGE, ARBEITSTAG |
| ISOWEEKNUM, WEEKNUM | ISOKALENDERWOCHE, KALENDERWOCHE |
| DATEDIF, DATEVALUE | DATEDIF, DATWERT |
German Excel separates arguments with semicolons, so =DATE(B4,B3,B2) becomes =DATUM(B4;B3;B2).
Which Excel versions have these functions?
Every function on this page works in all current versions, including Excel 2016 and 2019, Excel for Mac and Excel for the web. ISOWEEKNUM has been available since Excel 2013.
FAQ
How do I count the days between two dates?
Subtract the earlier date from the later one: =C2-B2. If only working days count, use NETWORKDAYS.
How do I add months to a date?
Use EDATE: =EDATE(B2,6) returns the date six months later. Adding 30 days per month drifts over longer periods.
How do I show the day name instead of a number?
Apply the custom number format dddd and the cell stays a date. As text, use =TEXT(B2,"dddd"), which returns Monday for January 5, 2026.