Menu
Cart 0

How to Subtract Dates in Excel

Posted by Roger Hyttinen on

In yesterday’s post, we looked at how to add two dates together in Excel. Today, we going to look at how to subtract dates.

If you want to calculate how much time has passed between two dates, you need simply subtract the oldest date from the most recent date. For example, if the value in cell A1 was 12/31/2014 and the value in cell B1 was 9/3/2014, your formula would read:

=A1 – B1

The resulting answer would be 4/28/1900 – hardly what we expected. In order to get the correct response, we need to format the cell where the formula resides as a Number (Select Number from the drop-down list on the Number group on the Home Ribbon). Our answer would then be 119 representing the number of days between the two dates.

To calculate the number of months between two dates, use the formula:

=MONTH(A1)-MONTH(B1)

Once again, you will need to apply a number format to the cell containing the formula. One important note: This formula will only work if the months occur in the same year.

To calculate the number of months between two dates falling in different years, use the following formula:

=((YEAR(A1)-YEAR(B1))*12)+MONTH(A1)-MONTH(B1))

In this formula, we take the oldest year, multiply it by 12 months, and then subtract it from the most recent year. Then, subtract the month of the oldest year from the most previous year and add it to the year result. To calculate the number of years between two dates, our formula would read:

=YEAR(A10)-YEAR(B1)


Share this post



← Older Post Newer Post →


Leave a comment

Please note, comments must be approved before they are published.