Excel Date Calculation Formula: How To

To calculate dates in Excel, you use simple arithmetic: add or subtract days with =A1+30, or use functions like DATEDIF, EDATE, and WORKDAY for months, years, or business days. The exact formula depends on what you need—days between dates, a future date, or age—and this article breaks down each one with examples.

Formula

The core formula for adding or subtracting days is:

New Date = Start Date + Number of Days

In Excel, a date is stored as a serial number—January 1, 1900 is 1, January 2 is 2, and so on. So adding 90 to a date simply adds 90 to that serial number. For months or years, use:

• EDATE(start_date, months) – adds or subtracts months (e.g., =EDATE(A1, 3) gives a date 3 months later).
• DATEDIF(start_date, end_date, "unit") – calculates the difference in days ("d"), months ("m"), or years ("y").
• WORKDAY(start_date, days, [holidays]) – adds business days, skipping weekends and optional holidays.

Each function expects a valid Excel date as the first argument. If you type a date directly, use DATE(2024,1,15) to avoid text-string errors.

Worked Example

Using the calculator above with start date January 15, 2024 and 90 days:

1. Excel stores 2024-01-15 as serial number 45306 (because it’s 45,305 days after 1900-01-01, plus the 1900 leap year bug).
2. Add 90: 45306 + 90 = 45396.
3. Convert 45396 back to a date: 45396 days from 1900-01-01 is April 14, 2024.

So the result is April 14, 2024. The calculator above gives the same answer because it uses the same arithmetic.

When to Use Each Date Formula

Choose the formula based on what you need:

• Days between two dates: =B1-A1 (if B1 is later) or =DATEDIF(A1,B1,"d"). The simple subtraction works because dates are numbers.
• Add weeks: multiply by 7, e.g., =A1+2*7 for 2 weeks.
• Add months (calendar months): =EDATE(A1, 6) for 6 months. Note that EDATE handles month-end correctly (Jan 31 + 1 month = Feb 28/29).
• Add years: =EDATE(A1, 12*years) or =DATE(YEAR(A1)+n, MONTH(A1), DAY(A1)).
• Business days: =WORKDAY(A1, 10) adds 10 working days, skipping Sat/Sun. Add holidays as a range: =WORKDAY(A1, 10, $H$2:$H$5).
• Age or tenure: =DATEDIF(A1, TODAY(), "y") gives complete years, "ym" gives remaining months, "md" gives remaining days.

For current date calculations, use TODAY() (updates daily) or NOW() (includes time).

How to Read the Result

Excel displays the result as a date if the cell is formatted as a date. If you see a number like 45396, the cell is formatted as General or Number. To fix: right-click the cell → Format Cells → Date → choose a format. If you want the number of days (not a date), format as General.

Be aware of time components. If your date cell includes a time (e.g., 2024-01-15 14:30), adding 90 gives 2024-04-14 14:30, which might display as a date only. For pure dates, ensure the cell has no time part.

Common Mistakes and How to Avoid Them

• Text vs. date: Typing “Jan 15, 2024� as text won’t calculate. Use DATE(2024,1,15) or a real date entry.
• Wrong unit in DATEDIF: "d" is days, "m" is months, "y" is years. Using "m" for minutes is a common error—there’s no minute unit in DATEDIF.
• EDATE vs. simple addition: =A1+30 adds 30 days, not 1 month. For calendar months, use EDATE.
• WORKDAY with negative days: =WORKDAY(A1, -5) goes back 5 business days—works fine, but be careful with holidays.
• Leap years: Adding 365 days to Feb 29, 2024 gives Feb 28, 2025, not Mar 1, because 2025 is not a leap year. For “same date next year� use EDATE(A1,12), which gives Feb 28.
• 1900 leap year bug: Excel treats 1900 as a leap year (it wasn’t). This only affects dates before March 1, 1900—rarely relevant.

Limitations and Edge Cases

Excel’s date system starts at January 1, 1900 (serial 1) and ends at December 31, 9999 (serial 2958465). Dates before 1900 are not supported. Also, DATEDIF is undocumented in some versions, but it works in Excel 2016 and later. For negative date differences (end before start), simple subtraction gives a negative number, but DATEDIF returns an error—use =IF(B1>A1, DATEDIF(A1,B1,"d"), -DATEDIF(B1,A1,"d")) to handle both directions.

When adding months with EDATE, if the start day doesn’t exist in the target month (e.g., Jan 31 + 1 month), Excel returns the last day of that month (Feb 28/29). This is usually what you want, but be aware if you need exact day counts.

Frequently Asked Questions

How do I calculate the number of days between two dates in Excel?

Subtract the earlier date from the later one: =B1-A1. Format the cell as General to see the number. Or use =DATEDIF(A1,B1,"d") which gives the same result but is more explicit.

What is the formula to add months to a date in Excel?

Use =EDATE(start_date, months). For example, =EDATE(A1, 6) adds 6 months. If you want to add 3 years, use =EDATE(A1, 36).

How do I calculate age in Excel from a birthdate?

Use =DATEDIF(birthdate, TODAY(), "y") for full years. For a more detailed age: =DATEDIF(birthdate, TODAY(), "y") & " years, " & DATEDIF(birthdate, TODAY(), "ym") & " months, " & DATEDIF(birthdate, TODAY(), "md") & " days".

Why is my date formula showing a number instead of a date?

The cell is formatted as General or Number. Right-click the cell, choose Format Cells, select Date, and pick a format. The underlying value is still correct; only the display changes.

How do I add business days to a date in Excel?

Use =WORKDAY(start_date, days). It automatically skips Saturdays and Sundays. To exclude holidays, add a range: =WORKDAY(start_date, days, holiday_range).

What does the #VALUE! error mean in date formulas?

It usually means one of the arguments is text that Excel can’t interpret as a date. Check that all date cells contain real dates (not text) and that you’re not using a function incorrectly, like DATEDIF with an end date earlier than the start date.

Related

• JS Calculator Code: How to Build One
• King Code Calculator: What It Is & How to Use
• Julian Code Calculator: Convert Dates Easily

in start | Start Date | - | 2024-01-15 | 0
in days | Days to Add | days | 90 | 0
out new_date | New Date | - | start + days | 0