Forum Discussion
Adding multiple date ranges.
I have multiple date ranges. (24/3/1988 to 23/5/1992, 15/3/1994 to 1/3/1995 , 14/11/1996 to 18/7/1998....) How to calculate the total time period in number of years, months and days in excel 4/12...
Starrysky1988 this formula is for one range only. I want to get the total. For example
| 4/12/2015 | to | 6/2/2019 | 4 years,1 months,21 days | |
| 9/8/2010 | to | 1/8/2012 | 1 years,4 months,0 days | |
| 5/3/2007 | to | 3/4/2008 | 0 years,10 months,1 days | |
| 03/05/2004 | to | 04/03/2006 | 1 years,10 months,1 days | |
| total | ?????? |
total of those time periods
Please take note if you want to include start date and end date in the calculation(For e.g, 1 Jan 22 to 3 Jan 22 as total 3 days result), you must add 1 to Datedif formula when calculating the days difference.
=Datedif(start_date,end_date,"md")+1
If you want total of years, months and days, you'd better calculate with 3 columns of year difference, months difference and days difference without combining into single formula.
Days
=Mod(Sum(All days),30)
Months
=Mod(Sum(All months)+Quotient(Sum(All days),30),12)
Years
=Sum(All years)+Quotient(Sum(All months)+Quotient(Sum(All days),30),12)
=Datedif(start_date,end_date,"md")+1
If you want total of years, months and days, you'd better calculate with 3 columns of year difference, months difference and days difference without combining into single formula.
Days
=Mod(Sum(All days),30)
Months
=Mod(Sum(All months)+Quotient(Sum(All days),30),12)
Years
=Sum(All years)+Quotient(Sum(All months)+Quotient(Sum(All days),30),12)