week 1 | saturday | week 2 | sunday | ||
BEE | 09:15 - 18:45 | BEE | 09:15 - 18:45 | =IFERROR(IF(OR(RIGHT(B2,5)+0=0,LEFT(E2,5)+0=0),"",IF(RIGHT(B2,5)+0>LEFT(E2,5)+0.5,"less than 12hr","")),"") | |
BRENDA | 12:00 - 21:30 | BILL (NEW STAFF) | this is new staff, i bet the outcome is blank, just like day off vs anytime, leave it blank | ||
BRIAN | BRENDA | 08:00 - 18:00 | since week 2 come with a new staff, brenda will go down one row, thus no outcome no matter less than / good enough | ||
BRIAN | 13:00 - 21:00 | same with brenda case, just go down one row caused by a new staff came |
Book1 | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | D | F | H | J | L | N | P | Q | ||||||||||
1 | 17.5 | 26.25 | 26.25 | #VALUE! | #VALUE! | #VALUE! | 17.5 | ||||||||||||
2 | |||||||||||||||||||
3 | Bee | 09:30 - 19:00 | 11:30 - 21:00 | 11:30 - 21:00 | 12:00 - 21:30 | 12:00 - 21:30 | #VALUE! | ||||||||||||
4 | Brenda | 00:00 - 00:00 | 09:15 - 18:45 | 09:15 - 18:45 | Unpaid Leave Day 1.0 | Unpaid Leave Day 1.0 | Unpaid Leave Day 1.0 | #VALUE! | |||||||||||
5 | Brian | 09:15 - 18:45 | 09:15 - 18:45 | 10:00 - 19:30 | 11:30 - 21:00 | 10:00 - 19:30 | #VALUE! | ||||||||||||
6 | Carlie | 09:15 - 18:45 | 09:15 - 18:45 | 12:00 - 21:30 | Vacation Leave Day 1.0 | Vacation Leave Day 1.0 | #VALUE! | ||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1, N1, L1, J1, H1, F1, D1 | B1 | =(SUMPRODUCT(RIGHT("00:00"&B3:B6,5)-LEFT(B3:B6&"00:00",5))-("00:45"*COUNTIFS(B3:B6,"?*",B3:B6,"<>00:00 - 00:00")))*24 |
Q3:Q6 | Q3 | =(SUMPRODUCT(RIGHT("00:00"&B3:O3,5)-LEFT(B3:O3&"00:00",5))-("00:45"*COUNTIFS(B3:O3,"?*",B3:O3,"<>00:00 - 00:00")))*24 |
H1, J1, L1 & column Q seems doesnt working fine, and might coursed by the inappropriate time value, like "Unpaid Leave Day 1.0" / "Vacation Leave Day 1.0"?But you already have formulae in those cells & they have nothing to do with this thread.
Should you have posted this to another of threads?