Thanks for your compliments. I will try to explain that Why is the result 2 years, that would be one surely and why I want D2 to be 2 not 14?
Well, 62 days are equal to 2 months and 2 days, 14 months are equal to 1 year 2 months. As I already told that I want to calculate the total period of different spells. So 60 days from "days" are equal to 2 months which added to months i.e. 14+2=16 months that means 1 year and 4 months so total period comes to 2 years 4 months and 2 days. Hope I have explained correctly.
However, drsarao's solution is exactly which I needed. Thank a lot. I have another related question, how can I subtract the above period i.e., 1 year 4 months and 2 days from a period of 18 years and 25 days?
No, I still don't see how you come up with that figure - even including both start and end dates the first period is 444 days, the second one is 25 days and the third 19....so grand total of 488 days - that isn't more than 2 years by any method I know
For the first period you are double counting, the 1 year in C2 is also being counted within the 14 months in D2, hence my suggestion to change the formula in D2 then you would only get the months left after whole years have been taken out
You are absolutely right, I have corrected the formula and now the result is 1 year 4 months and 2 days.
Will you please suggest me about my 2nd related question that how can I subtract the above result from 18 years and 25 days. I have to calculate the period in "year, month and days" format and not in whole year.
Thanks for your suggestion but result is "#VALUE!". I think it is my fault that I haven't clearly told that I want to subtract the result i.e, 1 year 4 months and 2 days from a total period of 18 years 0 month and 25 days. I didn't mean it by 25th day of January 1918.