I have been trying to find a way to add the values in cells, but the cells contain text as well as numbers. The sheet is to calculate overtime. i can get it to count the cells with the text in it and from there i multiply by 12 to get the hours worked, but i need to add number to the same cell in the case that person does not work 12 hours see example:
Person A worked 2 days ( one he worked for 12 hours and next 8 hours the total should be 10 but my calculation gives me 24. ( 2 days *12)
i need it to add the hours worked based on the persons name and hours inserted. if no hours inserted it should default to 12.
a formula i tried does not work, it reads false and reverts to days worked *12.
the formula ( was on different sheet) the ranges are 4 different ranges
=SUM(IF(ISNUMBER(FIND(C33;SearchRng));VALUE(LEFT(SearchRng;FIND(C33;SearchRng)-1));E33*12))
17 oct to 16 november overtime.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Date | 17-Dec | 18-Dec | 19-Dec | 20-Dec | 21-Dec | ||
2 | Day | Thu | Fri | Sat | Sun | Mon | ||
3 | Day Shift | |||||||
4 | O/T Person | |||||||
5 | O/T Person | person A 12 | ||||||
6 | O/T Person | |||||||
7 | Night Shift | |||||||
8 | O/T Person | |||||||
9 | O/T Person | |||||||
10 | O/T Person | |||||||
11 | ||||||||
12 | Date | 22-Dec | 23-Dec | 24-Dec | 25-Dec | 26-Dec | ||
13 | Day | Tue | Wed | Thu | Fri | Sat | ||
14 | Day Shift | |||||||
15 | O/T Person | person A 8 | ||||||
16 | O/T Person | |||||||
17 | O/T Person | |||||||
18 | Night Shift | |||||||
19 | O/T Person | |||||||
20 | O/T Person | |||||||
21 | O/T Person | |||||||
22 | Name | Ctrl No: | Days | Hours | ||||
23 | person A | 2 | 24 | |||||
test sheet |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C12:F12,C1:F1 | C1 | =B1+1 |
B13:F13,B2:F2 | B2 | =TEXT(B1,"ddd") |
B12 | B12 | =F1+1 |
E23 | E23 | =SUM(COUNTIF($B$3:$F$6,"*"&C23&"*"),COUNTIF($B$8:$F$10,"*"&C23&"*"),COUNTIF($B$15:$F$17,"*"&C23&"*"),COUNTIF($B$19:$F$21,"*"&C23&"*")) |
F23 | F23 | =E23*12 |
Person A worked 2 days ( one he worked for 12 hours and next 8 hours the total should be 10 but my calculation gives me 24. ( 2 days *12)
i need it to add the hours worked based on the persons name and hours inserted. if no hours inserted it should default to 12.
a formula i tried does not work, it reads false and reverts to days worked *12.
the formula ( was on different sheet) the ranges are 4 different ranges
=SUM(IF(ISNUMBER(FIND(C33;SearchRng));VALUE(LEFT(SearchRng;FIND(C33;SearchRng)-1));E33*12))