• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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

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

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
 
Top