Forum Discussion

Viswa88's avatar
Viswa88
Copper Contributor
Mar 26, 2022

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/2015 to6/2/2019 4 years,1 months,21 days
9/8/2010 to1/8/2012 1 years,4 months,0 days
5/3/2007 to3/4/2008 0 years,10 months,1 days
03/05/2004 to04/03/2006 1 years,10 months,1 days
  total  ??????
excel

4 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 26, 2022

    Viswa88 

    I'm not sure such logic works in general. Summing months and days separately depends on time scale you use, are leap years and months with 28,29, 30 and 31 days are inside. If take time scale from the beginning of Excel calendar that could be

    =LET(
  total, SUMPRODUCT($C$1:$C$4-$A$1:$A$4),
 DATEDIF(0,total, "y") & " years " &
 DATEDIF(0,total, "ym") & " months " &
 DATEDIF(0,total, "md") & " days")

    but results very depends on from which start date you calculate total number of days difference in the periods.

  • Starrysky1988's avatar
    Starrysky1988
    Iron Contributor
    Mar 26, 2022
    =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"
    • Viswa88's avatar
      Viswa88
      Copper Contributor
      Mar 26, 2022

      Starrysky1988 this formula is for one range only. I want to get the total. For example

      4/12/2015 to6/2/2019 4 years,1 months,21 days
      9/8/2010 to1/8/2012 1 years,4 months,0 days
      5/3/2007 to3/4/2008 0 years,10 months,1 days
      03/05/2004 to04/03/2006 1 years,10 months,1 days
        total  ??????

      total of those time periods

      • Starrysky1988's avatar
        Starrysky1988
        Iron Contributor
        Mar 26, 2022
        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)

Resources