Complex Formulas possibly using IF

R

Rallie

I am in the process of creating a worksheet for the entry of timesheet and
the costings of this timesheets. What I need to do is once I have enter the
total Hours that an employee has worked for a specific job in cell N7, then I
need the hours to breakdown according into Normal Time, Time and a half and
Double Time, but this is also specific to the day that the employee is
working.

Monday to Thursday the Breakdown is:
8Hrs - Normal Time
3hrs - Time and a half
Rest - Double

Friday's Breakdown
6Hrs - Normal Time
3hrs - Time and a half
Rest - Double

Saturday's Breakdown
3hrs - Time and a half
Rest - Double

Sunday's Breakdown
6Hrs - Normal Time
3hrs - Time and a half
Rest - Double

But I need it to take into account if he works less then 8 Normal Time when
working account several different jobs for the day.

I know that it is complex and hopefully you are able to assit me.
 
B

Barb Reinhardt

You could do something like this to figure out the time for each day

=SUMPRODUCT(--(A1:A10="Employee Name"),--(C1:C10 = Date(2008, 8,21)),(B1:B10))

Where
A1:A10 contain all of the employee names
B1:B10 contain the hours worked
C1:C10 contain the dates worked

You could replace Date(2008, 8,21) with
Date(Year(E1),Month(E1),Day(E1)) where E1 contains the date of interest

If you want to figure out the day of the week, use the WEEKDAY function.

=--
HTH,
Barb Reinhardt
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top