I have a spreadsheet that looks like this:
| Entry Date | Start | End | Name | Details |
|---|---|---|---|---|
| 9/13/2024 13:46:03 | 09/13/2024 | 09/13/2024 | James Jameson | Out—Called off-Emergency |
| 9/15/202413:46:45 | 09/30/2024 | 10/04/2024 | Jack Jackson | Out—Scheduled off-Paternity leave |
| 9/15/202413:43:53 | 09/16/2024 | 10/04/2024 | John Johnson | Out—Scheduled off-Vacation |
| 9/29/202413:44:49 | 09/30/2024 | 09/30/2024 | Jacob Jacobson | Out—Called off-Sick |
I would like to list out each WORKDAY beginning with Start Date and ending with End Date with the associated Name and Details for each date sorted by entry date. This would be an ARRAYFORMULA since I am using a form to populate the entries in the spreadsheet. This is what the result should look like (note that weekends are filtered out):
| Date | Name | Details |
|---|---|---|
| 09/13/2024 | James Jameson | Out—Called off-Emergency |
| 09/16/2024 | David Davidson | Out—Scheduled off-Vacation |
| 09/17/2024 | David Davidson | Out—Scheduled off-Vacation |
| 09/18/2024 | David Davidson | Out—Scheduled off-Vacation |
| 09/19/2024 | David Davidson | Out—Scheduled off-Vacation |
| 09/20/2024 | David Davidson | Out—Scheduled off-Vacation |
| 09/23/2024 | David Davidson | Out—Scheduled off-Vacation |
| 09/24/2024 | David Davidson | Out—Scheduled off-Vacation |
| 09/25/2024 | David Davidson | Out—Scheduled off-Vacation |
| 09/26/2024 | David Davidson | Out—Scheduled off-Vacation |
| 09/27/2024 | David Davidson | Out—Scheduled off-Vacation |
| 09/30/2024 | William Williamson | Out—Scheduled off-Paternity leave |
| 09/30/2024 | David Davidson | Out—Scheduled off-Vacation |
| 09/30/2024 | Andrew Anderson | Out—Called off-Sick |
| 10/01/2024 | William Williamson | Out—Scheduled off-Paternity leave |
| 10/01/2024 | David Davidson | Out—Scheduled off-Vacation |
| 10/02/2024 | William Williamson | Out—Scheduled off-Paternity leave |
| 10/02/2024 | David Davidson | Out—Scheduled off-Vacation |
| 10/03/2024 | William Williamson | Out—Scheduled off-Paternity leave |
| 10/03/2024 | David Davidson | Out—Scheduled off-Vacation |
| 10/04/2024 | William Williamson | Out—Scheduled off-Paternity leave |
| 10/04/2024 | David Davidson | Out—Scheduled off-Vacation |
I tried to modify player0's formula in the accepted Stack Overflow answer here, but I kept getting errors.
Does anyone have a recommendation for going about this?
Thank you.
You can try:
=SORT(SPLIT(TOCOL(
MAP(B2:B5,C2:C5,D2:D5,E2:E5,LAMBDA(s,e,n,d,LET(
x,SEQUENCE(1,e-s+1,s),
IFNA(FILTER(x,"S"<>LEFT(TEXT(x,"ddd")))&"❅"&n&"❅"&d)))),1),"❅"))