Excel Date Calculation Methods and Formulas

In Excel, calculate dates by adding or subtracting dates and day counts, or use functions such as DATE, EDATE, WORKDAY, and NETWORKDAYS for calendar-based tasks. Dates are stored as numbers, so a formula such as a later date minus an earlier date returns the number of days between them.

How Excel stores dates

Excel treats a date as a serial number: the number represents days counted from a starting point, and any time of day is stored as a decimal fraction. For example, noon is one-half of a day. This is why date arithmetic works: adding 1 advances a date by one day, while subtracting two date values produces an elapsed time in days.

In a new Windows workbook, Excel normally uses the 1900 date system. Some workbooks, particularly older workbooks created on a Mac, may use the 1904 date system. The same serial number can represent a different calendar date in the two systems, so changing a workbook’s date system can shift displayed dates. Avoid switching systems casually, especially in a workbook with existing dates. If dates appear about four years off after moving a file between workbooks, check whether the workbooks use different date systems.

A date may look like “Jan 15, 2025” or “1/15/25” while its underlying value remains numeric. Formatting changes how Excel displays that value; it does not change the date used in calculations. If a formula result appears as a number such as 45672, apply a date format rather than changing the formula.

Subtract dates to find elapsed days

For a straightforward duration, put the start date in A2 and the end date in B2. With those dates in A2 and B2, enter =B2-A2. If A2 is January 15, 2025, and B2 is February 10, 2025, the result is 26 days. Format the result as General or Number to show the count, rather than as a date.

This calculation counts the days that have elapsed, not both calendar dates as separate days. For a stay or event where the start and end dates are both included, add 1. With the same dates in A2 and B2, enter =B2-A2+1; the result is 27 inclusive days. The distinction matters for attendance, billing periods, and date ranges, so decide whether the task needs elapsed days or an inclusive count.

To calculate a deadline from a known date, add a number of days. With a start date in A2 and the number of calendar days to add in B2, enter =A2+B2. If A2 is January 15, 2025, and B2 is 30, the result is February 14, 2025. To count backward, subtract instead: with the same layout, =A2-B2 returns December 16, 2024.

Build dates with DATE and add months

Use DATE when year, month, and day are in separate cells or when a formula should construct a date explicitly. With the year in A2, month number in B2, and day in C2, enter =DATE(A2,B2,C2). If those cells contain 2025, 2, and 10, the result is February 10, 2025. DATE is also useful in formulas because it avoids relying on how a particular computer interprets a typed date such as 2/10/2025.

DATE adjusts values that go beyond ordinary month or day ranges. For example, a day value of 32 rolls into the following month. This can be useful in deliberate date arithmetic, but it may also conceal an input mistake; validate source data if an out-of-range day should be rejected rather than rolled forward.

For a calendar-month offset, use EDATE rather than adding a fixed number of days. With a start date in A2 and the number of months to move in B2, enter =EDATE(A2,B2). If A2 is January 31, 2025, and B2 is 1, the result is February 28, 2025, the last valid day of that month. A negative month count moves backward. EDATE is helpful for recurring monthly dates, although the end-of-month adjustment means that a sequence starting on the 31st will not always land on the 31st.

To return the last day of a month, use EOMONTH. With any date in A2 and a month offset in B2, enter =EOMONTH(A2,B2). If A2 is February 10, 2025, and B2 is 0, the result is February 28, 2025. A value of 1 returns March 31, 2025; a value of -1 returns January 31, 2025. These functions are available in Microsoft 365 Excel.

Calculate working days and business deadlines

Calendar-day arithmetic includes weekends. When a deadline should skip Saturdays and Sundays, use WORKDAY. With the start date in A2 and the number of working days to add in B2, enter =WORKDAY(A2,B2). If A2 is Friday, January 10, 2025, and B2 is 1, the result is Monday, January 13, 2025. WORKDAY does not count the starting date as the first added workday when the day count is positive.

To exclude holidays too, list holiday dates in D2:D10. With the start date in A2, the working-day count in B2, and holidays in D2:D10, use =WORKDAY(A2,B2,D2:D10). The holiday cells must contain real Excel dates; text that merely looks like a date may not be recognized. If a holiday falls on a weekend, it does not usually change the result because that date is already a nonworking day.

To count working days between two dates, use NETWORKDAYS. With a start date in A2, an end date in B2, and holidays in D2:D10, enter =NETWORKDAYS(A2,B2,D2:D10). For January 10 through January 13, 2025, with no holidays, the result is 2: Friday and Monday. NETWORKDAYS counts both endpoints when they are weekdays. Its standard pattern treats Saturday and Sunday as weekends; use NETWORKDAYS.INTL or WORKDAY.INTL when a schedule has different weekend days.

Find age and year-based differences

For a person’s age in completed years, DATEDIF can compare a birth date with a later date. With the birth date in A2 and the date to calculate age on in B2, enter =DATEDIF(A2,B2,"Y"). If A2 is June 15, 2000, and B2 is June 14, 2025, the result is 24; on June 15, 2025, it becomes 25. Using a date in B2 makes the result reproducible, unlike using the current date directly.

DATEDIF also accepts units such as “M” for completed months and “D” for days. It is a legacy function that may not appear in Excel’s autocomplete or function prompts, but it is supported. The start date must not be later than the end date; otherwise, the formula can return an error. For a precise age breakdown or unusual rules, define what “years” and “months” mean before choosing a formula.

YEARFRAC returns a year-based fraction between two dates, but its result depends on the selected day-count basis. With the start date in A2, end date in B2, and basis value in C2, enter =YEARFRAC(A2,B2,C2). For example, basis 1 uses an actual-days/actual-year convention, while basis 0 uses the US 30/360 convention. Different bases can produce different results. For contracts, accounting, or financial calculations, confirm the required convention rather than assuming one is universally correct.

Include times when calculating durations

Because Excel stores time as a fraction of a day, subtracting date-time values can calculate elapsed hours as well as days. With a start date and time in A2 and an end date and time in B2, enter =B2-A2. Format the result with a duration format such as [h]:mm to show total hours; ordinary h:mm can wrap after 24 hours and make a long duration look shorter than it is. To get hours as a number, with the same layout use =(B2-A2)*24.

If the end time is on the next day but only the time of day is recorded, subtracting the two cells may give a negative result. Prefer storing complete date-and-time values. If the task guarantees that the end time is later on the same or following day, a formula can account for a midnight crossing; do not use that shortcut if a negative interval could represent a genuine data error.

Format results and troubleshoot common problems

In Excel for Microsoft 365 on Windows, select the result cells and use Home > Number Format to choose Short Date, Long Date, or Number. For more control, press Ctrl+1 to open Format Cells and select a date or custom format. On Mac, Command+1 opens Format Cells; the same underlying date calculations apply. In Excel for the web, use the number-format controls on the Home tab. Formatting is separate from calculation, so a date difference displayed in date style may look strange even when the arithmetic is correct.

A date is left-aligned or will not calculate: it may be text rather than a numeric date. Re-enter it using a recognized date format or convert the source data, taking regional date order into account.
The result shows a serial number: apply a date format if the result is a date, or General/Number if it is a day count.
The result is unexpectedly negative: check whether the start and end cells are reversed, or whether a time-only value crosses midnight.
The result changes each day: formulas using TODAY or NOW are recalculated. Use a fixed date value when a report must preserve the date it was created.
Dates differ between files: check the workbook’s 1900 or 1904 date system and confirm that imported values were interpreted using the intended regional date order.

To insert a fixed current date manually on Windows, press Ctrl+; (Control plus semicolon). Excel for Mac uses Control+; for the current date as well. Unlike a formula, an inserted date does not update tomorrow. For a date that updates automatically, with the result in any chosen cell enter =TODAY(); it returns the current date according to the computer’s date settings. The related =NOW() returns the current date and time, and recalculates when Excel recalculates the workbook.

Frequently Asked Questions

How do I calculate the number of days between two dates in Excel?
Put the earlier date in A2 and the later date in B2, then enter =B2-A2. The result is elapsed days. Add 1 if the task requires counting both dates inclusively.

How do I add months to a date in Excel?
With the starting date in A2 and the number of months in B2, use =EDATE(A2,B2). Excel adjusts the day when the destination month is shorter, such as January 31 moving to February 28.

How can I calculate a deadline that skips weekends?
Use WORKDAY with a start date and a number of working days, for example =WORKDAY(A2,B2). Add a holiday-date range as the third argument if holidays should also be excluded.

Why does Excel show a number instead of a date?
Excel stores dates as serial numbers. Select the cell and apply a date format if the value represents a date; if it represents a duration in days, keep it formatted as a number.

Can Excel calculate age accurately?
For completed years, use DATEDIF with a birth date and an as-of date, such as =DATEDIF(A2,B2,"Y"). Use a fixed as-of date when you need a result that will not change over time.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.