#VALUE! error with text in calculation

[jroeder]

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 errors. I like to use a V or H instead of numbers so it calculates in a separate column. I get the #Value! error in the regular and overtime columns because of the text. I am trying to add to the current formulas in regular and overtime to convert the text to a zero value but I cant get anything to work. This is my current formula for regular time calculation:

=IF((D6+E6+F6+G6+H6+I6)>40, 40,SUM(D6+E6+F6+G6+H6+I6))

Can anyone think of a way to avoid the error if I have the letters V or H in any one of those cells?? I also attached one of the spreadsheets I use so you can see how it all works together. 

[#2]

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

[#3]

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. 

[#4]

Yes, I forgot OT is overtime. When it could be

for regular hours

=MIN(COUNTIF(D6:I6, ">0")*8,SUM($D6:$I6))

where COUNTIF calculates number of days with any hours worked (not zero, H and V)

For cumulative overtime

=MAX(0,SUM($D6:$I6)-J6)

By the way, we ignore Sunday in all calculations. If to include in all formulas ranges shall start from C column, not D