I WANT FORMULA IN THIS DATA
BETWEEN A WEEK MORE THAN 2 A (A=ABSENT)
THAN AUTOMATICALLY SUNDAY PICK A
YELLOW COLOR DATA IS SUNDAY
BETWEEN A WEEK MORE THAN 2 A (A=ABSENT)
THAN AUTOMATICALLY SUNDAY PICK A
YELLOW COLOR DATA IS SUNDAY
uplod.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | DATE | ATTENDENCE | WORKING TIME | OVERTIME | NO. OF NIGHTS | LATE TIME | ||
2 | 1-Aug | P | 8:00 | |||||
3 | 2-Aug | P | 8:00 | |||||
4 | 3-Aug | P | 8:00 | 15:00 | 1 | |||
5 | 4-Aug | A | 0:00 | |||||
6 | 5-Aug | A | 0:00 | 15:00 | 1 | |||
7 | 6-Aug | A | 0:00 | |||||
8 | 7-Aug | P | 8:00 | 15:00 | 1 | 0:30 | ||
9 | 8-Aug | P | 8:00 | |||||
10 | 9-Aug | A | 0:00 | 8:00 | ||||
11 | 10-Aug | P | 8:00 | 15:00 | 1 | |||
12 | 11-Aug | P | 8:00 | |||||
13 | 12-Aug | P | 8:00 | |||||
14 | 13-Aug | P | 8:00 | |||||
15 | 14-Aug | P | 8:00 | 15:00 | 1 | |||
16 | 15-Aug | P | 8:00 | |||||
17 | 16-Aug | P | 8:00 | |||||
18 | 17-Aug | P | 8:00 | 15:00 | 1 | |||
19 | 18-Aug | P | 8:00 | |||||
20 | 19-Aug | P | 8:00 | 15:00 | 1 | |||
21 | 20-Aug | P | 8:00 | |||||
22 | 21-Aug | P | 8:00 | 15:00 | 1 | |||
23 | 22-Aug | P | 8:00 | |||||
24 | 23-Aug | P | 8:00 | 8:00 | ||||
25 | 24-Aug | P | 8:00 | |||||
26 | 25-Aug | P | 8:00 | 15:00 | 1 | |||
27 | 26-Aug | P | 8:00 | |||||
28 | 27-Aug | P | 8:00 | 15:00 | 1 | |||
29 | 28-Aug | P | 8:00 | |||||
30 | 29-Aug | P | 8:00 | |||||
31 | 30-Aug | P | 8:00 | 8:00 | ||||
32 | 31-Aug | P | 8:00 | |||||
2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A3:A32 | A3 | =A2+1 |
C2:C32 | C2 | =IF(AND(B2=Sheet1!$B$2),Sheet1!$C$2,IF(AND(B2=Sheet1!$B$3),Sheet1!$C$3)) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
Sheet1!_FilterDatabase | =Sheet1!$B$2:$C$3 | C2:C32 |