Wednesday, September 12, 2012

How to calculate year, month and day between 2 cells in MS Excel 2010

Sometimes we need to calculate year, month and day between 2 cells in  Microsoft Excel.
For example, You have an employees database in excel that record the date they joined the company and then you want to know "how long each employee has worked for the company until now?"

In the such above case, I do the following in Microsoft Excel 2010:

  • I Input the "signed date" in cell C3 after "No" in cell C1 and "Full Name" in cell C2.
  • Make sure to assign "Date" as Cell Format for Cell C3.
  • I input =NOW() in Cell C4 and this will show the current date.
  • And the calculation begin in cell C5.
  • In cell C5, I put formula like this: =DATEDIF(C3,C4,"y") & " years, " & DATEDIF(C3,C4,"ym") & " months, " & DATEDIF(C3,C4,"md") & " days"
  • And the result of the above formula is like this: 19 years, 3 months, 11 days.


If you do not want to show the "day", just cut the last part of the formula (& DATEDIF(C3,C4,"md") & " days").

So now, you know how to calculate year, month and day between 2 cells in MS Excel 2010.

Do you have other formula to calculate the same above case?
Please share on the comment below.
Thank You

No comments:

Post a Comment