capitano10
New Member
- Joined
- May 22, 2019
- Messages
- 20
Your request is not clear. What is the specific text, depending on the 2 hours should be added in the cell Cut Hours?I would need to find a formula to add 2 hours to a monthly total if a specific text is in a cell.
Basically i would need to add 2 hours per every working day in the cell Cut Hours in red
Many hanks for the help
Considering that you use constantly the above transcript / syntax for the time substring (keeping the "-" separator between the start and the end time), and each working day should include 2 additional hours, see the next:specific text would be the shift time 9-17.30 or 7-15.30 and for every working our i would need to add 2 hours in the cut hours at the end of the month.
Basically i would need to know how many extra 2 hours the consultant would have to work in a month
Hope is clear thanks
Book1 | |||
---|---|---|---|
A | |||
1 | In 7.00 - 15.30 | ||
2 | Off | ||
3 | Off | ||
4 | extra day Back | ||
5 | In 9.00 - 17.30 | ||
6 | AAAAA 7.00 - 15.30 | ||
7 | Off | ||
8 | extra day Back | ||
9 | B / HOL | ||
10 | In 9.00 - 17.30 | ||
11 | In 9.00 - 17.30 | ||
12 | B / HOL | ||
13 | In 9.00 - 17.30 | ||
14 | In 7.00 - 15.30 | ||
15 | In 7.00 - 15.30 | ||
16 | B / HOL | ||
17 | Off | ||
18 | 0000 9.00 - 17.30 | ||
19 | Off | ||
20 | Off | ||
21 | HOLIDAY | ||
22 | HOLIDAY | ||
23 | HOLIDAY | ||
24 | HOLIDAY | ||
25 | HOLIDAY | ||
26 | Off | ||
27 | Off | ||
28 | HOLIDAY | ||
29 | In 7.00 - 15.00 | ||
30 | In 7.00 - 15.00 | ||
31 | 22 | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A31 | A31 | =SUMPRODUCT(LEN(A1:A30)-LEN(SUBSTITUTE(A1:A30,"-","")))*2 |