Ignore text in timecard calculation

[Rico Suave]

I am trying to develop a formula that breaks out regular and over-time on
employee timecards. The timecard cell entries can be numeric for work hours
or text for such things as V for vacation, or A for absent, and so on. I can
SUM the total hours for the week and the formula treats text days as zero so
the SUM function works. But, I cannot get the IF function to ignore text days
and only split regular and overtime from the week's work hours. It has to be
able to calculate each day because the regular work day is 8 hours, anything
over 8 hours in a day is overtime, so each day must be evaluated. My aim is
to create a standardized worksheet for supervisors to use that can also be
used to upload the split hours directly into a payroll summary worksheet.

 

[RagDyeR]

Hours are entered in A2 to A8.

For overtime total try:

=SUMIF(A2:A8,">8")-(8*COUNTIF(A2:A8,">8"))
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I am trying to develop a formula that breaks out regular and over-time on
employee timecards. The timecard cell entries can be numeric for work hours
or text for such things as V for vacation, or A for absent, and so on. I can
SUM the total hours for the week and the formula treats text days as zero so
the SUM function works. But, I cannot get the IF function to ignore text
days
and only split regular and overtime from the week's work hours. It has to be
able to calculate each day because the regular work day is 8 hours, anything
over 8 hours in a day is overtime, so each day must be evaluated. My aim is
to create a standardized worksheet for supervisors to use that can also be
used to upload the split hours directly into a payroll summary worksheet.