# Employee time sheet

#### allanwalton01

##### New Member
I have created a time sheet to help me in my position, however I have been asked to do the following
 Seven day operation Done this lol Monthly contractual costs calculated Overtime rate to be driven by employee/contract type Sick Pay calc based on contract type Agency costs to be able to be calculated from the rota schedule unit transfers to be able to be calculated accordingly in/out Summary showing holiday/sick/absence hours against each employee for the month Rota to be calculated based trading week
is there anyone will to advise how I do this as I am stuck

#### Attachments

• 55.9 KB Views: 4
Last edited:

#### Peter Bartholomew

##### Well-Known Member
I am not really suggesting you adopt the workbook below. I never use A1 references in a workbook so, in this case, I have used Tables and structured references. Dynamic array formulas are normally my preferred solution but I came to the conclusion that your problem is better suited to tables. That said …
The formula I settled on was
= 24 * SUMPRODUCT( SIGN(HolidayWeek1[@]="Holiday"), employeeFinishTimes - employeeStartTimes )
along with
= 24 * SUMPRODUCT( SIGN(HolidayWeek1[@]<>"Holiday"), employeeFinishTimes - employeeStartTimes )
The unusual feature is that the names 'employeeFinishTimes' and 'employeeStartTimes' are local to the sheet and given by
= INDEX( HoursWorked[@], {1,3,5,7,9,11,13} )
= INDEX( HoursWorked[@], {2,4,6,8,10,12,14} )

These create arrays of 7 columns, making downstream calculations easier.

#### Attachments

• 68.5 KB Views: 20

#### allanwalton01

##### New Member
Hi Peter
thanks for this , it seems when I put all the cells from Z just so #value am I doing something wrong

#### Peter Bartholomew

##### Well-Known Member
The formula I quoted before
= 24 * SUMPRODUCT( SIGN(HolidayWeek1[@]<>"Holiday"), employeeFinishTimes - employeeStartTimes )
works well enough using Office 365 but I have some concerns whether the array parameter
employeeFinishTimes - employeeStartTimes
will evaluate in full. It may be that the multiplication prior passing the arrays to SUMPRODUCT would be safer in that it would coerce the calculation to return the full array of seven values.
= 24 * SUMPRODUCT( (HolidayWeek1[@]<>"Holiday") * (employeeFinishTimes - employeeStartTimes) )
It may be that this works better on your version of Office.

#### Attachments

• 69.2 KB Views: 1

#### allanwalton01

##### New Member
yes it looks my office 365 is not working correctly as I said sent it to a mate who should be on same version but his is updated and works , it seems to work on my Mac office so will finish there and see what happens thanks again