Forum Discussion

jroeder's avatar
jroeder
Copper Contributor
Jan 03, 2019

#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...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 04, 2019

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

jroeder's avatar
jroeder
Copper Contributor
Jan 04, 2019

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. 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 04, 2019

    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

Resources