Time Sheet Formula - different rate at different times

1. Time Sheet Formula - different rate at different times

Hi all

Sorry if this has already been posted but i can see it

I want to make a time sheet where i type in the start time (A1) and finish time (B1) it will work out the following automatically

1, Total worked worked i have sorted this with [=SUM((B1-A1+(B1<A1))*24)]
2, Hours worked @ Normal rate between 8:30 and 17:00
3, Hours worked @ 1.25 rate before 8:30
4, Hours worked @ 1.25 rate between 17:00 and 24:00
5, Hours worked @ 1.33 rate after 24:00

can anyone help
I was looking for a long time for formulas like that, tried a lot of things but I never could get it to work.
Now I need one more rate in it, but still I cant get it to work.

The first hour, after 8 hours in the normal rate, needs to be @ 1.125 rate.
After that the 1.25 rate and 1.33 rate again.

Would be very thankful if someone could help me.  Register To Reply

2. Re: Time Sheet Formula - different rate at different times

The normal rate only applies to 8:30 to 17:00, or 8.5 hours. So what exactly needs to be 1.125? Just half an hour? Or all the hours beyond 8 hours in one day?  Register To Reply

3. Re: Time Sheet Formula - different rate at different times

The first hour after 8 normal working hours in the normal rate should be @ 1.125.
So the nineth hour in the normal block time. The 10th hour should be @ 125 rate again.

And if the nineth hour is outside the normal block time (8 till 17.30) then it should be @125 rate, like the sheet is now.  Register To Reply

4. Re: Time Sheet Formula - different rate at different times

Again, you can only have 30 minutes of extra time within the 8.5 hours between 8:30 and 17:00, not a full ninth hour.

Change the formula in D15 to this array formula, entered using Ctrl-Shift-Enter

=ROUND(SUM(IF(D8:D14>8/24,8/24+(D8:D14-8/24)*1.125,D8:D14))*24*E3,2)

This is used in the first sheet of the attached.

Or you could use an extra column, as in the second sheet of this workbook: time-sheet-upload.xlsx  Register To Reply

5. Re: Time Sheet Formula - different rate at different times

Hmm weird the formula gives an error.  Register To Reply

6. Re: Time Sheet Formula - different rate at different times

It may give an error if you do not array enter it - did you download the workbook that I posted? It worked in that file.  Register To Reply

7. Re: Time Sheet Formula - different rate at different times

Oh yeah I see sheet 2 now. That's how I want it.
I adjusted the times how it should be now and the time after 8 hours in the block time can be max one hour at the 1.125 rate, it gives now sometimes 2 as well.

Almost there, I am very happy and thankful Can u maybe have one more look at it? I added a v2 file.

I should really learn how to use the CSE formulas.  Register To Reply

8. Re: Time Sheet Formula - different rate at different times

Try this - the colored cells don't (and shouldn't) have formulas.

Also, the finish time should never be earlier than the start time for Friday - it the shift extends over midnight into Saturday, then it should be split over the two days.
Start to midnight
midnight to finish on Saturday

time-sheet-v3.xlsx  Register To Reply

9. Re: Time Sheet Formula - different rate at different times

Awesome! This is what I mean, thanks a lot   Register To Reply