Referencing multiple fields in a timesheet
I'm trying to work out the calculations necessary to pull information held about users who have multiple pay levels into a timesheet.
For example: Sam gets paid at £9.62 per hour for Project A, and £8.58 per hour for Project B. This information is already held in a user app as pay rates, with a calculation to work out the hourly rate based on the annual salary for each rate. However, other users earn differing amounts for the same project - ie. Joe earns £11.17 for Project A. This means I can't set a pay rate for each project; it has to be linked to the user.
I have an app which totals the monthly amount of hours based on individual entries - Sam does not work every day, so will need to input individual entries eg. 5th Oct 4 hours Project A, 6th Oct 3 hours Project B, 9th Oct 5 hours Project A, 10th Oct 4 hours Project B. Currently my app will calculate the total for the month as Sam's timesheet entries are linked to the monthly one.
What I need in the individual entry is for it to show their name (pulled from the monthly), then they can select date, hours and project.
The monthly one then needs to calculate the user's rate for a given project: If user = Sam and if Project = A then Hourly rate = [calculated field] and a calculation under each rate to show total hours * hourly rate.
Can anyone help?
-
Hi Susan,
I don't understand your setup. Does the hourly rate depend on the type of a Project (Type A, Type B) so that the hourly rate is a calculation mix of Project type and annualy salary (something like: Standard hourly rate *1.1 if type A, * 1 if type B)?
How does your user app look like, which data are in there?
Each time sheet should be linked to a user (user app) and to a project, it should have one or two date fields and a field which shows how many hours the user worked that day.
The further process depends on your answers to my above questions.Rainer
-
Hi Rainer
Thanks for your message. I may not have been clear enough in my description.
Most of our staff members only have 1 hourly rate (based on annual salary, which varies according to their role). However, some members have different rates for different projects. These projects don't have a fixed rate; it is down to the individual's setup.
So Staff member Joe is paid £9.62/hr on Project A, and £8.18/hr on Project B
Staff member Fred is paid £8.18/hr on Project A, and £7.95/hr on Project B
This shows the different hourly rates and how someone might have more than 1 rate.
The calculation app then has one entry per staff member per month, which totals the hours put in on another app (user selects the timesheet, selects date and inputs hours). This is then multiplied by a manually entered hourly rate.I need to be able to have the hourly rate selected based on which project / user i.e. "If Staff = Lis AND Project = Eco, hourly rate = rate 1 else if Staff = Lis AND Project = Dover, hourly rate = rate 2 else if Staff = Lis, hourly rate = rate 3 else if Staff = Alison AND Project = Eco, hourly rate = rate 1" etc
The problem is that hourly rates vary based on the user, so cannot be fixed against the project - so I need the calculation to not only reference staff and project, but to also reference the calculated hourly rate for each user based on it being that user.
I can take further screenshots etc if needed. Sorry if I'm not clear on my explanation!
Su
Please sign in to leave a comment.
Comments
2 comments