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

Question

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 Sample Spreadsheet containing the data I have put together. Unfortunately the only formula I have come across that autopopulates between dates is:

=ArrayFormula((TO_DATE(row(indirect("A"&Input!B2):indirect("A"&Input!C2)))))

however, this does not also bring across the contents of Column A and does not process numerous rows of data at a time.

Answer 1

Try:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(IF(DAYS(C2:C10, B2:B10)+1>=
 SEQUENCE(1, MAX(DAYS(C2:C10, B2:B10))), A2:A10&"×"&B2:B10+
 SEQUENCE(1, MAX(DAYS(C2:C10, B2:B10)), 0), )), "×"), "where Col2>0", ))