How to Calculate Date Difference in Excel

Knowing some simple formulae for calculating date difference can help you to calculate date difference in Excel. Giving the formulae for calculating number of days between two dates, number of months between two dates and number of years between two dates I have explained the steps about how to calculate date difference in Excel in simplified manner.

The functions which we have used are present in the ‘insert function’ tab of Excel, but Microsoft has hidden these functions, so we need to type these functions explicitly to get the date difference calculated.
How to Calculate Date Difference in Excel
Calculating number of days between two dates:

We use the following formulae for calculating difference between two dates:

This formula gives the number of days between two dates.

=A2 – A1

This formula gives the number of weekdays between two dates.

= NETWORKDAYS (StartDate, EndDate)

i.e.

= NETWORKDAYS (A1, A2)

  • If you want to calculate number of days between two dates, then enter start date in one cell and enter end date in another cell.
  • Suppose, you have entered start date in cell A1 and end date in cell A2.
  • Now, enter the formula ‘=A2 – A1’ in another cell (A3). You will get the number of days between two dates calculated in cell A3.
  • If you want to calculate the number of weekdays between two dates, then enter the formula ‘= NETWORKDAYS (A1, A2)’ in cell A4. You will get the number of weekdays between two dates.

Calculating number of months between two dates:

There are two different formulae that we can use in calculating the number of days between two dates. These formulae are as given below.

You can use the below formula if you want to calculate the number of days between two dates within same year.

= MONTH (A2) – MONTH (A1)

If you want to calculate the number of days between two months which are from two different years then you can use the below formula.

= (YEAR (A2) – YEAR (A1)) * 12 + MONTH (A2) – MONTH (A1))

  • Enter two dates between which you want to know the number of months in two different cells, suppose A1 and A2.
  • Now, enter the formula ‘= MONTH (A2) – MONTH (A1)’ in another cell (A3) and you will get the number of days between two months calculated in this third cell.
  • If you are using the two dates of different year then use the second formula i.e. ‘= (YEAR (A2) – YEAR (A1)) * 12 + MONTH (A2) – MONTH (A1))’ in cell A3.

Calculating number of years between two dates:

We use YEAR function to calculate number of years between the two dates. The formula for calculating years between two dates is given as

=YEAR (A2) – YEAR (A1)

  • We have to follow the similar process for calculating the number of years between two dates as we did in the above two processes.
  • Enter the start date in first cell A1 and second date in cell A2.
  • Enter the above formula ‘=YEAR (A2) – YEAR (A1)’ in the third cell A3. Number of years between two dates will be displayed in this third cell.

Calculating number of days before a particular date:

You can also calculate the number of days before a date using the formula. There are two different formulae that will help you in calculating the number of days before a particular date.

If you want to calculate the number of days from current date to a specific date then you can use the below formula.

=A1 – TODAY ()

The other formula to calculate the number of days before a date is same as the formula used in the calculation of number of days between two dates.

=A1 – A2

  • If you are using the first formula then insert the date in any cell say, A1.
  • Insert the formula ‘A1 – TODAY ()’ in another cell A2. In this formula the current date is taken from the system clock so make sure that your system clock is properly set to current date. If it is not set properly, then your answer may vary.
  • For calculating the number of days between two particular dates, use the second formula i.e. ‘=A1 – A2’ and enter two dates in two cells A1 and A2.

Tips:

  • Instead of entering the values of the cells manually, simply by clicking on the cell you will get the value of the cell in the formula.
  • The formulae given above can be used for Microsoft Excel 2007 only.


Related Content:

  1. How to Predict your Due Date
  2. How to Calculate Age
  3. How to Transpose Data in Excel
  4. How to Calculate BMR
  5. How to Calculate Loan Amount

Leave a Reply