non-numeric in a calculation

A

Albert.Harmse

I need to calculate hours worked for the employees. The staff reg got the
moths dates in row 1, and "time in" and "time out" in row 2. Column A is for
all the staff names. Say employee Harmse worked the following shifts for Jan.
on the 1st Jan (B2) started 7 and fin 17(C2) on the 2nd Jan also (D2) 7 till
17(E2) but on the 3rd Jan (F2) "AWOL" and "AWOL"(G2)on the 4thJan
(H2)"SICK"and "SICK"(I2). What formula will I use to calculate total hours
worked for the month. We used to replace the text with 0 and used a simple
formula, =(C3-B3)+(E3-D3)+(G3-F3)+(I2-H2) but since you cant go back and
check if they where sick or awol in months go by, they expect the register to
have all necessary abbreviations in. We use 5 abbreviations. AWOL. SICK. AL.
W/O & S/O. Is there a formula I can use to calculate total hours at work. We
use a Countif formula to calculate the awol, sick exe. All I need is actual
hours at work.
 
F

Fred Smith

First based on your description, the formula you are using is not:
=(C3-B3)+(E3-D3)+(G3-F3)+(I2-H2)

but
=(C2-B2)+(E2-D2)+(G2-F2)+(I2-H2)

It's always a bad idea to type in formulas, because of the possibility of
introducing errors, leading to delays in getting your answer, or getting
incorrect answers.

A quick solution to your problem is to use the Sum function which will
ignore text, as in:
=SUM(C2,E2,G2,I2)-SUM(B2,D2,F2,H2)

Regards,
Fred
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top