Employee time sheet

[allanwalton01]

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

 

[Peter Bartholomew]

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.

 

[allanwalton01]

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]

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.

 

[allanwalton01]

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