Date Formulas in Excel: DATE, EOMONTH, EDATE and More
· 7 min read
An Excel date formula works on dates as numbers. Each date is stored as the count of days since 1 January 1900, so 1 January 2025 is 45658, and the next day is 45659. That is why you can add 30 to a date to get the date 30 days later. The main functions:
| Task | Formula |
|---|---|
| Year, month or day of a date | =YEAR(A2), =MONTH(A2), =DAY(A2) |
| Build a date from parts | =DATE(2025, 3, 14) |
| Add days | =A2 + 30 |
| Add months | =EDATE(A2, 3) |
| Last day of the month | =EOMONTH(A2, 0) |
| Day of the week | =WEEKDAY(A2, 2) (Monday = 1) |
The examples below use a fixed list of contract start dates, so every result on this page stays the same. The sheets show dates as year-month-day.
Dates are numbers with a format
What you see in a date cell is a format laid over a number. Change the cell format to General and 31 January 2025 turns into 45688. Nothing about the value has changed, only how it is shown. Column D in the sheet below shows the same start dates without a date format.
So you can do arithmetic on dates: subtract one from another to get the days between them, or add a number to move forward. But a "date" that is really text, such as one pasted from a CSV, will not work with any of these functions. A text date usually sits on the left of the cell instead of the right, and =YEAR() on it returns #VALUE!.
Get the year, month, day and weekday from a date
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Contract | Client | Start | As a number | Year | Month | Day | Weekday (Mon = 1) |
| 2 | C-101 | Harbour Dental | 2025-01-31 | 45688 | 2025 | 1 | 31 | 5 |
| 3 | C-102 | Mill Lane Cafe | 2025-04-30 | 45777 | 2025 | 4 | 30 | 3 |
| 4 | C-103 | Okafor Legal | 2025-06-30 | 45838 | 2025 | 6 | 30 | 1 |
| 5 | C-104 | Station Gym | 2025-11-15 | 45976 | 2025 | 11 | 15 | 6 |
| 6 | C-105 | York Print Co | 2025-12-31 | 46022 | 2025 | 12 | 31 | 3 |
For the first contract, which starts on 31 January 2025:
=YEAR(C2) → 2025
=MONTH(C2) → 1
=DAY(C2) → 31These return plain numbers, which makes them easy to group by. A MONTH column next to your data is the simplest way to total sales by month with SUMIF; see SUMIFS with multiple criteria for that step.
WEEKDAY and the return type
WEEKDAY returns the day of the week as a number. Its second argument decides which day counts as 1:
| Return type | Numbers |
|---|---|
| 1 or left out | Sunday = 1 … Saturday = 7 |
| 2 | Monday = 1 … Sunday = 7 |
| 3 | Monday = 0 … Sunday = 6 |
Column H uses type 2, so Friday 31 January 2025 returns 5 and Saturday 15 November 2025 returns 6. With type 2, any result above 5 is a weekend day. Always write the second argument. With the default type 1, Saturday is 7 and Sunday is 1, and a test like >5 would wrongly catch Fridays.
To show the name of the day instead of a number, use TEXT: =TEXT(C2, "dddd") returns "Friday" and "ddd" returns "Fri".
Build a date with DATE
DATE takes a year, a month and a day and returns a real date:
=DATE(2025, 3, 14)Result: 14 March 2025, stored as 45730. Use DATE whenever you type a date inside a formula, for example in a SUMIFS condition. A typed text date such as "3/14/2025" is read differently depending on the computer's regional settings; DATE is read the same way everywhere.
DATE also rolls over sensibly. =DATE(2025, 14, 1) is 1 February 2026, because month 14 is two months into the next year. =DATE(2025, 3, 0) is the day before 1 March, which is 28 February 2025. That is one way to find the last day of a month, although EOMONTH below is easier to read.
Combined with YEAR and MONTH, DATE gives the first day of a date's month:
=DATE(YEAR(C2), MONTH(C2), 1)For the first contract that is 1 January 2025. A "month start" column like this is a common way to group dates by month in a pivot table or chart.
Add days, months and month ends
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Contract | Start | Term (months) | Plus 30 days | Renewal date | Month end | First of month |
| 2 | C-101 | 2025-01-31 | 1 | 2025-03-02 | 2025-02-28 | 2025-01-31 | 2025-01-01 |
| 3 | C-102 | 2025-04-30 | 3 | 2025-05-30 | 2025-07-30 | 2025-04-30 | 2025-04-01 |
| 4 | C-103 | 2025-06-30 | 6 | 2025-07-30 | 2025-12-30 | 2025-06-30 | 2025-06-01 |
| 5 | C-104 | 2025-11-15 | 12 | 2025-12-15 | 2026-11-15 | 2025-11-30 | 2025-11-01 |
| 6 | C-105 | 2025-12-31 | 2 | 2026-01-30 | 2026-02-28 | 2025-12-31 | 2025-12-01 |
Excel formula to add days to a date
Add the number of days:
=B2 + 30Result: 2025-03-02. February 2025 has 28 days, so 30 days after 31 January lands in March. Subtract to go back. To skip weekends and holidays, use WORKDAY, covered in days between dates.
Excel formula to add months to a date: EDATE
"One month later" is not the same as "30 days later". EDATE moves by whole calendar months:
=EDATE(B2, C2)C-101 starts on 31 January with a one-month term, so its renewal is 2025-02-28: February has no 31st, so EDATE uses the last day of the month. C-105 starts on 31 December 2025 with two months, and renews on 2026-02-28. Use a negative number to go back: =EDATE(B2, -1) is one month earlier. For years, multiply: =EDATE(B2, 12*2) adds two years.
If EDATE shows a number like 45716 instead of a date, the formula is fine. Format the cell as a date.
Excel formula for end of month: EOMONTH
EOMONTH returns the last day of a month, a number of months away from the start date:
=EOMONTH(B2, 0)Result: 2025-01-31. The second argument counts months: 0 is the same month, 1 the next month's end, -1 the previous month's end. Month ends are useful for due dates ("payment due by the end of next month") and for reports that cut off at month end.
TODAY: a date that changes
=TODAY() returns the current date and =NOW() the current date and time. Both recalculate every time the workbook does, so a formula like =TODAY() - B2 shows a different answer tomorrow. That is right for a live "days overdue" column. For a report you send on a fixed date, type the report date into a cell and point your formulas at it instead, so the numbers do not change after you send it. That is also why the examples on this page use fixed dates.
Common mistakes
Dates stored as text. If YEAR or MONTH returns #VALUE!, or a date sits on the left of the cell, it is text. Convert it with DATEVALUE, or with Text to Columns on the whole column.
Adding 30 for a month. As column D shows, 30 days drifts away from the calendar month. Use EDATE for months and EOMONTH for month ends. AI assistants often write =B2+30 when asked for "one month later". It looks right for most dates and is wrong at month ends.
Leaving out WEEKDAY's return type. The default puts Sunday first. Write 2 so Monday is 1 and weekends are 6 and 7.
Hard-coding dates as text in formulas. ">=1/3/2025" means 3 January in one country and 1 March in another. Use ">="&DATE(2025,3,1).
FAQ
What is the date formula in Excel?
=DATE(year, month, day) builds a date, for example =DATE(2025, 3, 14). To read parts of an existing date, use YEAR, MONTH and DAY.
How do I add months to a date in Excel?
Use EDATE: =EDATE(A2, 3) adds three months. If the start is the 31st and the target month is shorter, EDATE returns the last day of that month.
How do I get the last day of the month in Excel?
Use =EOMONTH(A2, 0) for the same month, or =EOMONTH(A2, 1) for the end of next month.
How do I get the day of the week from a date?
=WEEKDAY(A2, 2) returns 1 for Monday through 7 for Sunday. =TEXT(A2, "dddd") returns the name, such as Friday.
How do I get the quarter from a date?
Divide the month by 3 and round up: =ROUNDUP(MONTH(C2)/3, 0) returns 1 to 4. Join it with text for a label like Q1, as shown in how to concatenate in Excel.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- Rebuild delivery dates from three columns
- Invoice due at the end of next month
- Six-month review dates for new reps
- Every order lands in the wrong monthAudit