Is there any formula that I can use to show up each month according to start & end date in spreadsheet.

Example:

Start Date:2022-07-22
End Date:2022-10-22

I expected formula to extract value something like this

Jul - Aug - Sep - Oct

I've tried formula

=IF(A2="","",IF(TEXT(B2,"MM")-TEXT(A2,"MM")>1,CONCATENATE(TEXT(A2,"MMM")&" - "&text(EDATE(A2,1),"MMM")&" - "&TEXT(B2,"MMM")),IF(TEXT(A2,"MMM")=TEXT(B2,"MMM"),TEXT(A2,"MMM"),CONCATENATE(TEXT(A2,"MMM")&" - "&TEXT(B2,"MMM"))))) but it only give me correct value if there is up to 3 month period between start & end date.

CodePudding user response：

Use sequence(), mod() and join(), like this:

=arrayformula( map( 
  A2:A, B2:B, 
  lambda( 
    start, end, 
    if( 
      isdate(start) * isdate(end), 
      join( 
        " - ", 
        mod( 
          sequence( 
            12 * (year(end) - year(start))   month(end) - month(start)   1, 
            1, month(start) - 1 
          ), 
          12 
        )   1 
      ), 
      iferror(1/0) 
    ) 
  ) 
) )

CodePudding user response：

Get the difference in dates in months using DATEDIF and get dates in each intervening month using EOMONTH SEQUENCE and convert the end of month dates to TEXT:

Drag fill formula:

=ARRAYFORMULA(JOIN(" - ",TEXT(EOMONTH(A2,SEQUENCE(DATEDIF(A2,EOMONTH(B2,),"M") 1)-1),"mmm")))

Or as a self adjusting array formula:

=MAP(A2:INDEX(A:A,COUNTA(A:A)),LAMBDA(a, ARRAYFORMULA(JOIN(" - ",TEXT(EOMONTH(a,SEQUENCE(DATEDIF(a,EOMONTH(OFFSET(a,0,1),),"M") 1)-1),"mmm")))))

This should be faster and efficient than getting all the dates and filtering them out one by one, thereby reducing space and time complexity.