Forum Discussion

ecantu's avatar
ecantu
Copper Contributor
Aug 12, 2023

Define conscutive months

Hi everyone!   I am trying to get the average length between two dates only for consecutive months.    To get the average length between dates I am using =AVERAGE(DATEDIF(A2,A1,"d"),(DATEIF(A3,A2...
excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Aug 12, 2023

ecantu 

=((YEAR(A2)=YEAR(A1))*(MONTH(A2)=MONTH(A1)+1))+((YEAR(A2)=YEAR(A1))*(MONTH(A2)=MONTH(A1)-1))
=IF(A1-A2<0,A2-A1,A1-A2)
=AVERAGEIFS(C2:C7,B2:B7,1)

AVERAGEIFS with these helper formulas in cells B2 and C2 returns the expected result in my sheet.

Resources