Rate changes after 40 hours and need to calculate total cost?

[y_not]

My spreadsheet needs to allow hours to be entered daily (Sun - Sat) then produce a daily total value (based on an hourly rate). I found that bit straightforward BUT if a person works more than 40 hours the hourly rate changes (higher) and higher again after 50 hours

So:
a person works 12 hours a day from Mon-Sat
hourly rates are £10 up to 40 hours; £14 between 40 -50 hours; £18 over 50 hours

Mon, Tue, Wed are simply 12 hours x £10 per day so (total hours so far = 12) Mon = £120, (Total hours so far = 24) Tue = £120, (Total hours so far = 36) Wed = £120
Thu (total hours so far = 48) = 4 x £10 PLUS 8 x £14 so Thu = £152
Fri (Total hours so far = 60) = 2 x £14 PLUS 10 x £18 = £208

I was going to add a sample spreadsheet but I just don't know where to start with this one.

Thanks in advance

[newdoverman]

I have created a small time sheet example that might give you some ideas for what you want.

[MarvinP]

Hi y_not,

Find the attached where I've created a DataDNR table (Dynamic Named Range) in columns A:C. Then you can simply add employee, date and hours worked to the bottom of the table. You will need to REFRESH the Pivot Table to update it after entering a few rows in the data.

Then a Median function is used in columns H:I to get what I think you want. The coolest one is in I7. Study this one to make sure it is what you need.

Find the attached with how I would do your problem

[y_not]

Thanks Gents, MEDIAN (?) now thats a new one to me!

Think I need to spend a few hours getting to understand these and see if they produce the output I need. Your help is, as always, greatly appreciated.

Once I know that these sort my issue I'll mark as [SOLVED]

[popipipo]

@MarvinP
How did you get those dates in the pivot table (cells: E6, E14 and E22)
I can't reproduce it.

[MarvinP]

Hey,

I do a lot of random building of table data. For the dates I used a function of "=Today() - RandBetween(1,40)" Then I pulled it down to get a bunch of dates. I then did a formula of "="Name" & RandBetween(1,7)" to build the names and for hours something like "=RandBetween(1,10)" I then selected the 3 formulas, all in row 2 and pulled them down about 200 rows. To keep them from changing I then copy those columns and paste them using values only.

In the Pivot Table you can click on the DATE and GROUP them by day. This then allows a "number of days" range for each group.

I think that answers your question?

[popipipo]

Thanks MarvinP

[y_not]

Gents, many thanks for the replied however, it appears that the responses show the weekly cost whereas my customer wants to know how much it's cost him per day with a running total for the week to date and then a final figure at the end of the week.
Are you able to help some more ... please?

p.s. still getting to grips with MEDIAN ;-))

[jason.b75]

See if this helps.

The times are entered as numbers, not times, i.e. 12 for 12 hours, not 12:00.
The latter would need some changes to the formula used, which can be easily done if required.

[MarvinP]

Median gives back the "number in the middle".

So for the first 40 hours you want a certain rate. If the number is 10 then =Median(0, 10, 40) is 10. If the number is 55 then =Median(0, 50, 40) is 40.

[y_not]

See if this helps.

The times are entered as numbers, not times, i.e. 12 for 12 hours, not 12:00.
The latter would need some changes to the formula used, which can be easily done if required.

HI, The file supplied seemed to work brilliantly until I hit an issue. Using the example supplied, I changed the hours to zero for Saturday/Sunday and then 7.5 hours for every other day. A total of 37.5 hours for the week. Based on this all hours should have been at the £10 rate but, oddly, the Friday hours are split between 2.5 hours at £10 and 5 hours at £14 whereas because the total hours are under 40 all hours should calculate at the £10 rate?

Sorry to be a nuisance but can someone please advise what I am doing wrong?

[jason.b75]

Nothing you're doing wrong, there was an error in my formula.

Enter this one in B3

Please Login or Register  to view this content.

and this one in B4

Please Login or Register  to view this content.

Then drag them both right to H3 and H4 respectively.

[y_not]

Many thanks Jason - absolutely perfect now! Much appreciated