Forum Discussion
#VALUE! error with text in calculation
I use a spreadsheet for payroll to enter time cards since I have multiple companies. I have a nice formula to calculate regular hours vs. overtime, but when I try to add vacation and holidays I get e...
Hi,
You may use formulas like below taking into account SUM ignores any texts
REG: =MIN(40,SUM($D6:$I6)) OT: =COUNTIF($D6:$I6,"O")*8 VAC: =COUNTIF($D6:$I6,"V")*8 HOL: =COUNTIF($D6:$I6,"H")*8
Please see attached for the first record
Thank you very much for responding! It made me so happy to not see that error. The formulas works for everything except OT. Regular stops at 40 hours, but the OT doesn't calculate anything. If you put all 12's in the days you can see what I mean.
While i'm thinking of it, Holidays are included in our OT calculations. Can you think of anything that would stop the Regular hours at 32 when there is an "H", while keeping the other part of the formula in tact?? I think that's where my main issue lies, trying to get more than one scenario in the same formula.