Calculating a timesheet

Question

Level 1

24 points

Calculating a timesheet

I need to come up with a time sheet for hours of employees. In Row 1, I have listed the days of the week. In Column A, I have the two-hour shifts broken down. I want to be able to write someone's name in B2 (the Sunday from 7:30am - 9:30am shift) and then have the number of hours that he is serving calculated somewhere else in the table. See the screenshot. Because Joe worked two two-hour shifts, there is a "4" next to his name. Because Tony worked four two-hour shifts, there is an 8 next to his name. You get the idea. It's probably a very easy formula, but I just need help.

Thanks,

Mark

iMac, OS X Mountain Lion

Posted on Aug 6, 2015 10:03 AM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

36,278 points

Aug 6, 2015 1:54 PM in response to marknovom

If you have only one person per shift, then this is one way:

In B2, copied across and down:

=COUNTIF(Table 1::B,$A2)*2

Also, check out the Employee Schedule template at File > New (then locate in the Template Chooser under Business).

SG

Reply

Answer 2

Hiroto

Level 5

7,461 points

Aug 6, 2015 2:02 PM in response to marknovom

Hello

You may try something like this.

Table 1 (excerpt) A13 Joe A14 Tony A15 Bill A16 Mark B13 =COUNTIF(B$2:B$10,$A13)*2 B14 =COUNTIF(B$2:B$10,$A14)*2 B15 =COUNTIF(B$2:B$10,$A15)*2 B16 =COUNTIF(B$2:B$10,$A16)*2 C13 =COUNTIF(C$2:C$10,$A13)*2 C14 =COUNTIF(C$2:C$10,$A14)*2 C15 =COUNTIF(C$2:C$10,$A15)*2 C16 =COUNTIF(C$2:C$10,$A16)*2 D13 =COUNTIF(D$2:D$10,$A13)*2 D14 =COUNTIF(D$2:D$10,$A14)*2 D15 =COUNTIF(D$2:D$10,$A15)*2 D16 =COUNTIF(D$2:D$10,$A16)*2 E13 =COUNTIF(E$2:E$10,$A13)*2 E14 =COUNTIF(E$2:E$10,$A14)*2 E15 =COUNTIF(E$2:E$10,$A15)*2 E16 =COUNTIF(E$2:E$10,$A16)*2 F13 =COUNTIF(F$2:F$10,$A13)*2 F14 =COUNTIF(F$2:F$10,$A14)*2 F15 =COUNTIF(F$2:F$10,$A15)*2 F16 =COUNTIF(F$2:F$10,$A16)*2 G13 =COUNTIF(G$2:G$10,$A13)*2 G14 =COUNTIF(G$2:G$10,$A14)*2 G15 =COUNTIF(G$2:G$10,$A15)*2 G16 =COUNTIF(G$2:G$10,$A16)*2 H13 =COUNTIF(H$2:H$10,$A13)*2 H14 =COUNTIF(H$2:H$10,$A14)*2 H15 =COUNTIF(H$2:H$10,$A15)*2 H16 =COUNTIF(H$2:H$10,$A16)*2 I13 =SUM(B13:H13) I14 =SUM(B14:H14) I15 =SUM(B15:H15) I16 =SUM(B16:H16)

Notes.

Formula in B13 can be filled down and right across range B13:H16.

Formula in I13 can be filled down.

Table is built with Numbers v2.

Good luck,

H

Reply

Answer 3

marknovom Author

Level 1

24 points

Aug 19, 2015 2:40 AM in response to Hiroto

Hiroto,

Thank you! Love the formula, but now I've encountered another problem and I thought I could solve it on my own, but the formula is not working. The shifts are now not all 2 hours each. As you can see in the screenshot, the first and last shift are 3 hours and the shifts in the middle are 2 hours. I tried to do the formula, but as you can see it's saying "Error".

Can you help?

Thanks,

Mark

Reply