0

There are two dates : 01-11-2008 and 28-02-2017 (in dd/mm/yy format). I want excel to calculate the difference between these two dates and give the output in whole years.

If the difference between months is greater than Six(6) months then it should be rounded of to one year or else ignore.

I have inserted the following formula which is giving the result of 9 years whereas it should be 8 years.

=ROUNDUP((YEAR(LDate)-YEAR(EDate))+(IF(MONTH(LDate)-MONTH(EDate)>6,1,0))/12,0) 

As to how 8 years are derived is shown as follows (dd/mm/yy):

01-11-2008 to 31-12-2008 -- 2 month
01-01-2009 to 31-12-206  -- 96 Months
01-01-2017 to 28-02-2017 -- 2 months
               total       100 months

100/12 = 8.33 i.e 8 years.

Can some body give me a correct formula on this please?

  • you know YEAR(LDate)-YEAR(EDate)= 9 already you need to check it – yass Feb 19 '17 at 12:30
  • =ROUND((LDate-EDate)/365,0) this formula will do – yass Feb 19 '17 at 12:37
  • @yass Please don't provide answers in the comments. If you would like to help, post an answer. – Excellll Feb 22 '17 at 19:39
1

If you want the difference to be based on months, you could simply use this:

=ROUND(DATEDIF(EDate,LDate,"m")/12,0)

If you want it to be based on days, use the formula yass provided in the comments:

=ROUND((LDate-EDate)/365,0)
1

You can use the DATEDIF formula as follows

I am assuming you have Start Date in cell C1 and End date in D1,the formula goes like this

=SUM(DATEDIF(C1,D1,"Y"),IF(DATEDIF(C1,D1,"YM")>6,1,0))

But try giving the date in MM/DD/YYYY format,it will fetch the correct answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.