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...
=Datedif(start_date,end_date,"y") =Datedif(start_date,end_date,"ym") =Datedif(start_date,end_date,"md") You may combine them into single formula as below. =Datedif(start_date,end_date,"y")& "Years "& Datedif(start_date,end_date,"ym")& "Months " & Datedif(start_date,end_date,"md")& "Days"
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)
