Auto-populate dates between list of dates in Google Sheets and link with additional column-CodePudding

I have a spreadsheet that lists when an employee requests leave away from the business.

What formula can I use to produce the below output based on the data fed into the spreadsheet please?

I require:

Separate rows to be generated to show each individual day that the employee is off for, linked to their name
The list to include all employees in the list

This is a

CodePudding user response：

One way to do that is with sequence(), like this:

=arrayformula( 
  query( 
    split( 
      flatten( 
        if( 
          ( D2:D <= sequence( 1, max(D2:E) - min(D2:E)   1, min(D2:E) ) )
          *
          ( E2:E >= sequence( 1, max(D2:E) - min(D2:E)   1, min(D2:E) ) ), 
          A2:A & "→" & trim( to_date( sequence( 1, max(D2:E) - min(D2:E)   1, min(D2:E) ) ) ), 
          iferror(1/0) 
        ) 
      ), 
      "→", false, true 
    ), 
    "where Col2 is not null", 0 
  ) 
)

CodePudding user response：

Another:

=ArrayFormula(IFERROR(QUERY(SPLIT(FLATTEN(FILTER(A2:A&"~"&IF((B2:B SEQUENCE(1,60))>C2:C,,B2:B SEQUENCE(1,60)),A2:A<>"")),"~",1,0),"Select * WHERE Col2 Is Not Null"),{"",""}))

This will handle up to 60 days off at a time. You can just set the two instances of 60 to your maximum number of contiguous days off per person.