To enable a formula with variable rows, I had to add an extra row at the end of each list like this:
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S |
---|
1 | | (SUN) | (MON) | (TUE) | (WED) | (THU) | (FRI) | (SAT) | | | | | (SUN) | (MON) | (TUE) | (WED) | (THU) | (FRI) | (SAT) |
2 | Alex | | | 09:30-10:00
AA | 13:30-21:45
AA | 13:30-21:45
AA | 09:30-10:00
AA | | | | | Alex | | | 09:30 - 19:00 | 13:30 - 23:00 | 13:30 - 23:00 | 09:30 - 19:00 | |
3 | | | | 10:00-19:00
BB | 21:45-22:15
BB | 21:45-22:15
BB | 10:00-19:00
BB | | | | | Bella | | 09:00 - 18:30 | 13:30 - 23:00 | 11:00 - 20:30 | | 09:30 - 19:00 | 09:00 - 18:30 |
4 | | | | | 22:15-23:00
CC | 22:15-23:00
CC | | | | | | Bethia | 09:00 - 18:30 | 12:00 - 22:30 | | 13:00 - 22:30 | 13:00 - 22:30 | | |
5 | Bella | | 09:00-14:15
AA | 13:30-14:15
AA | 11:00-20:30
AA | | 09:30-14:15
AA | 09:00-14:15
AA | | | | Chelsea | | 09:00 - 18:30 | 09:30 - 19:00 | 09:30 - 20:00 | | | 09:30 - 19:00 |
6 | | | 14:15-18:30
BB | 14:15-21:30
BB | | | 14:15-19:00
BB | 14:15-18:30
BB | | | | Cherries | | | | | | 09:30 - 17:30 | |
7 | | | | 21:30-23:00
CC | | | | | | | | Cherry | | | 12:00 - 23:00 | 11:00 - 21:00 | 09:30 - 20:00 | | |
8 | Bethia | 09:00-09:30
AA | 12:00-22:30
AA | | 13:00-22:30
AA | 13:00-22:30
AA | | | | | | End | | | | | | | |
9 | | 09:30-18:30
BB | | | | | | | | | | | | | | | | | |
10 | Chelsea | | 09:00-09:30
AA | 09:30-14:15
AA | 09:30-10:00
AA | | | 09:30-19:00
AA | | | | | | | | | | | |
11 | | | 09:30-18:30
BB | 14:15-19:00
BB | 10:00-20:00
BB | | | | | | | | | | | | | | |
12 | Cherries | | | | | | 09:30-17:30
AAA | | | | | | | | | | | | |
13 | Cherry | | | 12:00-21:45
AA | 11:00-21:00
AA | 09:30-10:00
AA | | | | | | | | | | | | | |
14 | | | | 21:45-22:15
BB | | 10:00-20:00
BB | | | | | | | | | | | | | |
15 | | | | 22:15-23:00
CC | | | | | | | | | | | | | | | |
16 | End | | | | | | | | | | | | | | | | | | |
<tbody>
</tbody>
Sheet1
Array Formulas
Cell | Formula |
---|
M2 | {=IF(INDEX(B:B,MATCH($L2,$A:$A,0))="","",TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(B:B,MATCH($L2,$A:$A,0)):INDEX(B:B,MATCH($L3,$A:$A,0)-1),5)),1)),"hh:mm")&" - "&TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX(B:B,MATCH($L2,$A:$A,0)):INDEX(B:B,MATCH($L3,$A:$A,0)-1),7,5)),0)),"hh:mm"))} |
---|
<tbody>
</tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself |
<tbody>
</tbody>
The formula is also now quite complex
WBD