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.
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:
| Start Date | End Date | Months |
|---|---|---|
| 2022-07-01 | 2022-10-30 | Jul - Aug - Sep - Oct |
| 2022-08-02 | 2022-08-31 | Aug |
| 2022-07-03 | 2022-11-01 | Jul - Aug - Sep - Oct - Nov |
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.