Timetable problem

[Albert.Harmse]

Hi
I need to calculate the time worked for the employees on a weekly and
monthly basis, but knowing all employees they are not always at work. So we
use abbreviations like i.e AWOL, SICK exe. What formula will I use to add any
text as zero hours worked.

Any help with this will be greatly appreciated.

Regards

Albert.Harmse

 

[IanC]

Hi
I need to calculate the time worked for the employees on a weekly and
monthly basis, but knowing all employees they are not always at work. So
we
use abbreviations like i.e AWOL, SICK exe. What formula will I use to add
any
text as zero hours worked.

Any help with this will be greatly appreciated.

Regards

Albert.Harmse

I don't understand your problem. AWOL, SICK etc are not numeric, so in any
calculation they are effectively zero.

 

[Per Jessen]

Hi

Just use =Sum(A1:A10)

It will sum numeric values in the range only.

Regards,
Per

 

[Albert.Harmse]

Maybe this will put some light on my prob. We have a "time in" and "time out"
Row for every day of the month ( Row 1 & 2) with all the names in Column A.
Lets say Mr Harmse worked in Jan the following shifts. on the 1st Jan (B3) 7
and (C3) 17. 2nd Jan (D3) 7 and 17 (E3) on the 3rd Jan (F3) "AWOL" and
"AWOL"(G3). We used replace the "Awol" with 0 and do a simple calculation of
=(C3-B3)+(E3-D3)+(G3-F3). but as soon as there is text in the row it gives a
#value! error. What formula can I use to calculate the hours worked?

Please Help
Albert.harmse

 

[Ashish Mathur]

Hi,

Try this. In B2, D2 and F2, type In. In C2, E2 and G2 type Out. Then use
the following formula

=SUMIF(B2:G2,"Out",B3:G3)-SUMIF(B2:G2,"In",B3:G3)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com