Mar 25 2022 07:07 PM - edited Mar 25 2022 08:00 PM
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 | 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 | ?????? |
Mar 25 2022 07:15 PM
Mar 25 2022 07:55 PM
@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
Mar 25 2022 10:26 PM
Mar 26 2022 02:49 AM
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.