or i need to use your formula, then sum
Book1 | |||
---|---|---|---|
A | |||
1 | 09:15 - 18:45 | ||
2 | 09:15 - 18:45 | ||
3 | 10:45 - 21:00 | ||
4 | 13:00 - 21:00 | ||
5 | |||
6 | 34.25 | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A6 | A6 | =(SUMPRODUCT(RIGHT("00:00"&A1:A4,5)-LEFT(A1:A4&"00:00",5))-("00:45"*COUNTA(A1:A4)))*24 |
Book1 | |||
---|---|---|---|
C | |||
51 | 10:00 - 19:30 | ||
52 | |||
53 | |||
54 | 09:15 - 18:45 | ||
55 | 09:15 - 18:45 | ||
56 | 11:30 - 21:00 | ||
57 | |||
58 | 11:30 - 21:00 | ||
59 | 00:00 - 00:00 | ||
60 | 09:15 - 18:45 | ||
61 | |||
62 | |||
63 | |||
64 | 09:15 - 18:45 | ||
65 | 09:15 - 18:45 | ||
66 | 11:30 - 21:00 | ||
67 | 09:15 - 18:45 | ||
68 | |||
69 | 11:30 - 21:00 | ||
70 | |||
71 | 11:30 - 21:00 | ||
72 | 11:30 - 21:00 | ||
73 | 09:15 - 18:45 | ||
74 | |||
75 | 09:15 - 18:45 | ||
76 | 00:00 - 00:00 | ||
77 | |||
78 | 09:15 - 18:45 | ||
79 | 09:15 - 18:45 | ||
80 | 11:30 - 21:00 | ||
81 | 11:30 - 21:00 | ||
82 | 09:15 - 18:45 | ||
83 | 00:00 - 00:00 | ||
84 | 165.25 | ||
RESULT-1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C84 | C84 | =(SUMPRODUCT(RIGHT("00:00"&C51:C83,5)-LEFT(C51:C83&"00:00",5))-("00:45"*COUNTA(C51:C83)))*24 |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
'RESULT-1'!Print_Area | ='RESULT-1'!$A$39:$AD$85 | C84 |
it should be 166.25
=(SUMPRODUCT(RIGHT("00:00"&C51:C83,5)-LEFT(C51:C83&"00:00",5))-("00:45"*COUNTIFS(C51:C83,"?*",C51:C83,"<>00:00 - 00:00")))*24
Book1 | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | 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 | 43.75 | ||||||||||||
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 | 43.75 | ||||||||||||
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 |