1

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.

Here's a link to the sample spreadsheet

TheMaster
  • 45,448
  • 6
  • 62
  • 85
byrmn__
  • 59
  • 6
  • Your sheet is private. Make it public. – Harun24hr Sep 28 '22 at 06:27
  • Done, kindly check. – byrmn__ Sep 28 '22 at 06:44
  • Your question can be greatly improved if you add input table and expected output table to the question. [Tables](https://webapps.stackexchange.com/a/161855/) are a better alternative than spreadsheets to show your data structure. If you share spreadsheets, make sure to also add images of your sheet to avoid closure of your question, as questions here must be [self](https://meta.stackoverflow.com/a/260455) [contained](https://meta.stackexchange.com/a/149892). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), when you share Google files. – TheMaster Sep 28 '22 at 07:30
  • Im sorry, im a newbie here, thank you for the input sir. – byrmn__ Sep 28 '22 at 10:53
  • oh sorry, my bad, its done, thank you sir – byrmn__ Sep 29 '22 at 08:23

3 Answers3

2

For single cell can try-

=JOIN("-",UNIQUE(INDEX(TEXT(SEQUENCE(B2-A2+1,1,A2),"mmm"))))

For spill array-

=BYROW(A2:INDEX(B2:B,MATCH(9^9,B2:B)),LAMBDA(x,JOIN("-",UNIQUE(INDEX(TEXT(SEQUENCE(INDEX(x,2)-INDEX(x,1)+1,1,INDEX(x,1)),"mmm"))))))

See your sheet.

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
1

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

=arrayformula( map( 
  A2:A, B2:B, 
  lambda( 
    start, end, 
    if( 
      isdate(start) * isdate(end), 
      join( 
        " - ", 
        text( 
          edate( 
            start, 
            sequence( 
              12 * (year(end) - year(start)) + month(end) - month(start) + 1, 
              1, 0 
            ) 
          ), 
          "MMM" 
        ) 
      ), 
      iferror(1/0) 
    ) 
  ) 
) )
doubleunary
  • 13,842
  • 3
  • 18
  • 51
1

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.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • well thank you for your help, really appreciate it, so far this formula is the simplest as well as easy to understand for a newbie like me who trying to understand formula like this – byrmn__ Sep 28 '22 at 11:29
  • @byrmn__ Great. Consider accepting it, if you find it better than others. – TheMaster Sep 28 '22 at 11:57