Dates in Excel: how to calculate days, months, ages and working days

Working out how many days are left until a due date, someone’s age or the working days in a project looks simple until Excel returns a strange number, a row of hash signs or an error. The problem is rarely the formula itself. It is usually how Excel understands dates.

In this guide you will see how Excel stores dates, how to subtract and add time periods, how to calculate months and ages with DATEDIF, how to count working days with holidays and what to do when an imported date does not behave like one.

How Excel stores dates

To Excel, a date is a whole number with a date format applied. Each day adds 1, starting from 1 January 1900, which is 1. That is why 1 January 2025 is the number 45658 and 15 March 2025 is 45731. Times are the decimal part: 12:00 noon equals 0.5.

You can check this yourself: type a date, select the cell and change the format to General from Home › Number. You will see the serial number. This idea explains almost everything else:

• Subtracting two dates gives the number of days between them.
• Adding a number to a date moves it forward by that many days.
• If the result shows up as a date when you expected days, it is only a formatting issue.

Calculating the days between two dates

This is the most common calculation: days between an order and its delivery, between an invoice and its payment, between opening and closing a support ticket. Simply subtract the earlier date from the later one.

In C2 type the formula and drag it down:

=B2-A2

If you see a date such as 14 January 1900 instead of 14, Excel has copied the format of the source cells. Change the format of column C to General or Number and the number of days will appear.

To calculate the days left until a deadline, use today’s date with TODAY(), which updates every time you open the file:

=B2-TODAY()

A negative result means the deadline has already passed. You can combine it with conditional formatting to highlight values below zero in red.

Months, years and ages with DATEDIF

Subtracting dates gives days, but months do not always have the same length. To count complete months or years, use DATEDIF, an old function that still works in every version of Excel even though it does not appear in AutoComplete or in the Insert Function dialog. You have to type it in full.

Its syntax is DATEDIF(start_date,end_date,unit). The start date must be earlier than the end date; otherwise it returns the #NUM! error. With 15 Jan 2024 in A2 and 14 Jul 2025 in B2, these are the results for each unit:

Notice that the result in months is 17, not 18: on 14 July there is still one day to go before the month is complete. DATEDIF only counts complete periods.

=DATEDIF(A2,B2,"M")

Calculating age

If the date of birth is in B2, the current age in years is:

=DATEDIF(B2,TODAY(),"Y")

And if you want text such as “34 years and 5 months”, join two calculations with the & operator:

=DATEDIF(B2,TODAY(),"Y")&" years and "&DATEDIF(B2,TODAY(),"YM")&" months"

Avoid the "MD" unit in important calculations: Microsoft warns that it can give incorrect results in some cases, especially around the end of the month.

Adding days, months and month ends

To add days, just add a number. If an invoice issued on the date in A2 is due in 30 days:

=A2+30

Adding months is different, because “a month” is not always 30 days. Use EDATE, which follows the calendar. For example, for a review three months after the date in A2:

=EDATE(A2,3)

If the start date is 31 Jan 2025 and you add one month, the result is 28 Feb 2025: Excel moves to the last day of the month when the day does not exist. A negative number subtracts months.

To get the last day of the month for a date, useful for “end of month” payment terms, use EOMONTH:

=EOMONTH(A2,0)

Counting and adding working days

Delivery times and project deadlines often count only business days. NETWORKDAYS counts the days from Monday to Friday between two dates, both included, and can exclude a list of holidays.

1. Type the holidays in a separate column, for example F2:F10, one per cell and as real dates.
2. Put the start date in A2 and the end date in B2.
3. Write the formula with the holiday range locked with $ so you can copy it.

=NETWORKDAYS(A2,B2,$F$2:$F$10)

Between 1 Apr 2025 and 30 Apr 2025 there are 22 days from Monday to Friday. If your holiday list contains two dates that fall on weekdays that month, the result will be 20.

Calculating a delivery date in business days

If what you need is the resulting date, use WORKDAY. Starting on Friday 7 Mar 2025, ten business days later is Friday 21 Mar 2025:

=WORKDAY(A2,10,$F$2:$F$10)

When a date is not a date

The most frequent problem appears when you import data from a CSV file, a bank or another program: the dates arrive as text. They look fine, but formulas fail or return #VALUE!. Some warning signs:

• The date is aligned to the left in a cell with no manual alignment.
• When you change the format to General, no serial number appears.
• When sorting, “02/01/2025” comes before “15/12/2024” because it is sorted as text.

To confirm it, this formula returns TRUE if A2 is a real date and FALSE if it is text:

=ISNUMBER(A2)

The quickest fix is Data › Text to Columns: select the column, click Next twice and, in the third step, choose Date with the right order (DMY if the day comes first). If the dates come as year-month-day text (2025-03-15), you can rebuild them with a formula:

=DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))

If you often work with CSV files, converting them to XLSX first with our free CSV to Excel tool gives you a workbook that is ready to open. Even so, check the date column with ISNUMBER before calculating, especially if the file mixes formats from different countries.

Other common mistakes

• Cell full of #####: the column is too narrow or the result is a negative date. Widen the column or check the order of the subtraction.
• Two-digit years: “01/02/30” may be read as 1930 or 2030 depending on your settings. Always type the year with four digits.
• Days and months swapped: a file in month/day format turns 03/04 into 4 March. Check with dates where the day is greater than 12.

Conclusion

Working with dates in Excel is easy once you remember that every date is a number. Subtract to get days, use DATEDIF for months, years and ages, EDATE and EOMONTH for calendar deadlines and NETWORKDAYS or WORKDAY for business days. Before calculating, check that your dates are real dates and not text: it will save you most errors.

Frequently asked questions