How to calculate the difference between two dates in Excel

0
36

Excel – How to calculate the difference between two dates

The following formulas show how to calculate the difference between two dates.

The difference can be measured by:

  • days
  • months
  • years
  • days – ignoring years
  • days – ignoring months and years
  • months – ignoring years
  • combined years, months, days

Note: the DATEDIF function DOES NOT round calculations – so only whole figures are given.

* examples below use date format DD/MM/YYYY

A B C D E
1 Start date End date Difference Formula Description
2 01/01/2010 15/06/2015 1991 =DATEDIF( A2, B2, “D” ) days
3 65 =DATEDIF( A2, B2, “M” ) months
4 5 =DATEDIF( A2, B2, “Y” ) years
5 165 =DATEDIF( A2, B2, “YD” ) days – ignoring years
6 14 =DATEDIF( A2, B2, “MD” ) days – ignoring months and years
7 5 =DATEDIF( A2, B2, “YM” ) months – ignoring years
8 5 Years, 5 Months, 15 Days =IF(DATEDIF(A2,B2,”Y”)=1,DATEDIF(A2,B2,”Y”)&” Year, “,DATEDIF(A2,B2,”Y”)&” Years, “)&IF( DATEDIF(A2,B2,”YM”)=1,DATEDIF(A2,B2,”YM”) & ” Month, “,DATEDIF(A2,B2,”YM”)& ” Months, “) &IF(DATEDIF(A2,B2,”MD”)=1,DATEDIF(A2,B2,”MD”)&” Day”,DATEDIF(A2,B2,”MD”)&” Days”)
SHARE
Previous articleNotePad++ Silent Install using SCCM
Next articleHow to calculate the difference in Excel
mm
Hi, I’m Ravinder Jaiswal. I’m the person behind this website. Thank you for visiting the website! My website is all about Microsoft, VMware, Red Hat, Citrix and other technologies. I have 14+ years of IT experience. I am AZURE, MCSA 2016, MCSE 2016, RHCSA 7, RHCE 7, VCP-DCV 6.5, VCP-DTM 6.1, ITILv3 certified.