The simplest way to subtract dates in Excel

To find how many days are between two dates, subtract the earlier date from the later date in a cell. If your start date is in cell A1 and your end date is in cell B1, type =B1-A1 and press Enter. Excel will show you the number of days between them.

This works because Excel stores dates as numbers — January 1, 1900 is 1, January 2, 1900 is 2, and so on. When you subtract one date from another, you get the difference in days as a plain number. No special formatting or functions are needed for a basic calculation.

If the result shows as a decimal or a date instead of a whole number, you may need to change how that cell is formatted. Right-click the cell with your result, select Format Cells, choose Number from the Category list on the left, and click OK. The cell will then display the number of days clearly.

Key Takeaways

  • Subtract the earlier date from the later date using a simple formula like =B1-A1 to get the number of days between them.
  • If your result shows as a date instead of a number, right-click the cell and format it as a Number rather than a Date.
  • Use the DATEDIF function to calculate months or years between dates, which simple subtraction cannot do.
  • When dates are entered as text instead of actual dates, Excel will not recognize them — check that your date cells are formatted as dates first.

Calculating months or years between dates with DATEDIF

If you need to know how many months or years have passed between two dates, use the DATEDIF function instead. Type =DATEDIF(A1,B1,"M") to get the number of complete months, or =DATEDIF(A1,B1,"Y") to get complete years. The letter in quotes tells Excel what unit to measure.

DATEDIF accepts several different units. Use "D" for days (though simple subtraction is faster), "M" for months, "Y" for years, "MD" for days ignoring the month and year, "YM" for months ignoring the year, and "YD" for days ignoring the year. For example, =DATEDIF(A1,B1,"YM") tells you how many months have passed since the same date last year, without counting the years themselves.

The earlier date must always go first in DATEDIF. If you reverse them, Excel will show an error. Also, DATEDIF only works with actual dates — if your cells contain text that looks like dates, the function will not recognize them and will return an error message.

When dates are stored as text and will not calculate

Sometimes dates in your spreadsheet look correct but will not subtract or work with DATEDIF. This usually means Excel is treating them as text rather than as actual dates. You can tell because the number will be left-aligned in the cell instead of right-aligned, or a small green triangle appears in the corner.

To fix this, select all the cells with dates that are not working. Go to the Data menu at the top and click Text to Columns. In the window that opens, click Next twice to skip the first two screens, then make sure the column format is set to Date and the date format matches how your dates are written. Click Finish, and Excel will convert them to real dates that you can calculate with.

If you only have one or two problem cells, a faster method is to type the date again directly into the cell, making sure to use the same format as the others. Then press Enter. Excel will recognize it as a date from the start.

Handling dates that include time

When your dates include hours and minutes — for example, "1/15/2024 2:30 PM" — subtracting them still works, but the result includes a decimal. The whole number part is the days, and the decimal represents the time difference as a fraction of a day. For example, 5.5 means 5 full days plus 12 hours.

If you only want the number of complete days and do not care about the hours, wrap your subtraction in the INT function. Type =INT(B1-A1) to round down to the nearest whole day. This removes the decimal and gives you only the day count.

Alternatively, if you want to see the time difference as hours or minutes, you can format the result differently. Subtract the dates as usual, then right-click the cell, select Format Cells, choose Time from the Category list, and pick a format that shows hours and minutes. The result will display as a time duration instead of a decimal number.

Calculating age from a birthdate

To find someone's age in years from their birthdate, use DATEDIF with today's date. Type =DATEDIF(A1,TODAY(),"Y") where A1 contains the birthdate. The TODAY() function automatically inserts the current date, so the formula updates every day without you having to change it.

This formula gives you complete years only — it does not count partial years. If someone was born on March 15, 2000, and today is March 14, 2024, the formula will show 23, not 24, because their 24th birthday has not yet arrived.

If you want to show age in years and months, use two cells. In the first, type =DATEDIF(A1,TODAY(),"Y") for years. In the second, type =DATEDIF(A1,TODAY(),"YM") for the remaining months. Then you can display them together — for example, "23 years, 11 months".

Common mistakes when subtracting dates

The most frequent error is forgetting that the earlier date must come first. If you type =A1-B1 and A1 is the later date, you will get a negative number. Reverse the order to =B1-A1 and the result will be positive. Negative numbers are not wrong — they just mean you subtracted backwards.

Another common problem is mixing date formats in the same column. If some cells use "1/15/2024" and others use "January 15, 2024" or "15-Jan-24", Excel may treat some as text and others as dates. Before you calculate, make sure all your dates are formatted the same way. Select the entire column, right-click, choose Format Cells, pick Date, and choose one format for all of them.

If your formula returns an error like #VALUE!, the most likely cause is that one or both cells contain text instead of a date. Check that both cells are formatted as dates, not as text. You can also try the Text to Columns method described earlier to convert any text dates to real dates.

Frequently Asked Questions

Why does my date subtraction show a date instead of a number?

Excel is formatting the result as a date instead of a number. Right-click the cell with the result, select Format Cells, choose Number from the Category list, and click OK. The cell will then display the day count as a number.

Can I calculate the difference in weeks instead of days?

Yes. Subtract the dates as usual to get days, then divide by 7. Type =(B1-A1)/7 to get the number of weeks. If you want only complete weeks without decimals, wrap it in INT: =INT((B1-A1)/7).

What does the #VALUE! error mean when I try to subtract dates?

One or both of your cells contain text that looks like a date, not an actual date. Select the cells, go to Data > Text to Columns, click Next twice, make sure Date is selected as the format, and click Finish. This converts text dates to real dates.

How do I calculate the difference between dates in different time zones?

Excel does not have a built-in time zone function. If both dates include times and are already adjusted to the same time zone, simple subtraction works. If they are in different time zones, you must manually adjust one of them first by adding or subtracting hours before you subtract the dates.

Can DATEDIF work with dates from different years?

Yes. DATEDIF works across any date range, whether they are in the same year or decades apart. Just make sure the earlier date goes first. For example, =DATEDIF("1/1/2020","12/31/2024","D") will correctly calculate the days between those two dates.