Hi everyone,
I'm trying to get the following sheet to work but got stumped. The bottom block trees to match its time column to the times set in the block above. But, for some reason, for times greater than or equal to 13:00 (military time) it shows #N/A. It looks like it has something to do with the processing of military times but I'm not sure how to get around it.
Thanks in advance for your help.
Juan
I'm trying to get the following sheet to work but got stumped. The bottom block trees to match its time column to the times set in the block above. But, for some reason, for times greater than or equal to 13:00 (military time) it shows #N/A. It looks like it has something to do with the processing of military times but I'm not sure how to get around it.
Thanks in advance for your help.
Juan
Tasklist v2 for question v0.1.xlsm | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | |||||||||||
2 | |||||||||||
3 | Day starts | 9:00 | |||||||||
4 | |||||||||||
5 | Tasks | Length | Start | End | |||||||
6 | Top 3 | 1 | Thnk & plan | 00:30 | 09:00 | 09:30 | |||||
7 | 2 | Task 2 | 01:00 | 09:30 | 10:30 | ||||||
8 | 3 | Task 3 | 00:30 | 10:30 | 11:00 | ||||||
9 | Top 5 | 4 | Task 4 | 00:30 | 11:00 | 11:30 | |||||
10 | 5 | Task 5 | 02:00 | 11:30 | 13:30 | ||||||
11 | Unplanned | 6 | Task 6 | 00:30 | 13:30 | 14:00 | Things seem to start to go wrong when the hour is => 13:00 | ||||
12 | 7 | Task 7 | 00:30 | 14:00 | 14:30 | ||||||
13 | 8 | Task 8 | 00:30 | 14:30 | 15:00 | ||||||
14 | 9 | Task 9 | 01:30 | 15:00 | 16:30 | ||||||
15 | 10 | Task 10 | 00:30 | 16:30 | 17:00 | ||||||
16 | Match | ||||||||||
17 | Slot | Time | Task | Interruptions | Length | Task # | only | ||||
18 | 1 | 9:00 | Thnk & plan | 1 | 1 | ||||||
19 | 2 | 9:30 | Task 2 | 2 | 2 | ||||||
20 | 3 | 10:00 | Task 2 | 2 | #N/A | This #N/A is correct | |||||
21 | 4 | 10:30 | Task 3 | 3 | 3 | ||||||
22 | 5 | 11:00 | Task 4 | 4 | 4 | ||||||
23 | 6 | 11:30 | Task 5 | 5 | 5 | ||||||
24 | 7 | 12:00 | Task 5 | 5 | #N/A | This #N/A is correct | |||||
25 | 8 | 12:30 | Task 5 | 5 | #N/A | This #N/A is correct | |||||
26 | 9 | 13:00 | Task 5 | 5 | #N/A | This #N/A is correct | |||||
27 | 10 | 13:30 | Task 6 | 6 | 6 | ||||||
28 | 11 | 14:00 | Task 6 | 6 | #N/A | This should match to task 7 (line 7 above) | |||||
29 | 12 | 14:30 | Task 6 | 6 | #N/A | This should match to task 8 (line 8 above) | |||||
30 | 13 | 15:00 | Task 6 | 6 | #N/A | This should match to task 9 (line 8 above) | |||||
31 | 14 | 15:30 | Task 6 | 6 | #N/A | ||||||
32 | 15 | 16:00 | Task 6 | 6 | #N/A | ||||||
33 | 16 | 16:30 | Task 6 | 6 | #N/A | This should match to task 10 (line 8 above) | |||||
34 | 17 | 17:00 | Task 6 | 6 | #N/A | ||||||
35 | 18 | 17:30 | Task 6 | 6 | #N/A | ||||||
36 | 19 | 18:00 | Task 6 | 6 | #N/A | ||||||
37 | |||||||||||
Dayplan |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C6:C15 | C6 | =ROW()-ROW($C$6)+1 |
F6 | F6 | =F3 |
G6:G15 | G6 | =F6+E6 |
F7:F15 | F7 | =IF(C7=1,F$3,F6+E6) |
D7:D15 | D7 | ="Task "&TEXT(C7,"0") |
B18:B36 | B18 | =ROW()-ROW($B$18)+1 |
C18:C36 | C18 | =DayStartTime+(B18-1)*"0:30" |
D18:D36 | D18 | =OFFSET($D$5,$G18,0) |
G18:G36 | G18 | =IF(ISNA(MATCH(C18,F$5:F$16,FALSE)),G17,MATCH(C18,F$5:F$16,FALSE)-1) |
H18:H36 | H18 | =MATCH(C18,F$5:F$16,FALSE)-1 |