# 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

thank you

kevin

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

there are examples of your question on the forum.

but when do you get 1,25 (rule 3) and when do you get 1,33 (rule 5)

how will you define that?

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

there are examples of your question on the forum.

(just enter timesheet in the search box)

but when do you get 1,25 (rule 3) and when do you get 1,33 (rule 5)

how will you define that?

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

hi there

Its rule 3 (rate 1.25) then at 24:00 its rule 5 (rate 1.33)

could you send me a link where the examples are

thank you

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

00.01 till 08:30 (this is before 8:30) is 1,25.

when do you get 1,33 (because > 24:00 is the same as 00:01 => where you get 1,25)

Did you tried the tip below?
(just enter timesheet in the search box)

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

sorry

if i work passed 24:00 i get 1.33

eg start work on monday 8:00 and finish tuesday 02:00 i would get
rule 1: 18 hr
rule 2: 8.5 hr
rule 3: 0.5 hr
rule 4: 7 hr
rule 5: 2 hr

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

Hi toxic27

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
How come one gets a 1.33 rate if you work after 24:00, but then you say that if one works before 08:30 you get a rate of 1.25!

See the attached file, you did not mention breaks or weekend working so I have omitted these.

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

thank you for you help this is just what a wanted

thank you

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

I have been looking for this for a long time as well. I am aware this is an old topic, but maybe someone can help me with one more thing.
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.

I tried a lot of things but can not really find out how to fix it in the above file. Hope someone can help me, would be very thankful.

10. ## Re: Time Sheet Formula - different rate at different times

Originally Posted by ZeRoo_
I have been looking for this for a long time as well. I am aware this is an old topic, but maybe someone can help me with one more thing.
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.

I tried a lot of things but can not really find out how to fix it in the above file. Hope someone can help me, would be very thankful.
Oops, saw this was already in the [SOLVED] section, so I made a new topic: http://www.excelforum.com/excel-form...ent-times.html
sorry bout that

11. ## Re: Time Sheet Formula - different rate at different times

Hi,

I would like to use the template you uploaded (289-Time Sheet Formula - different rate at different times.xlsx‎) but I need 30 min break deducted each day. The break is to be deducted from the highest number of hours worked that day e.g. if there's 2 hours at rate 1, 6hrs at rate 2, and 2hrs at rate 3. I need 30 mins deducted from "rate 2" hours.

Can you or anyone else help?

12. ## Re: Time Sheet Formula - different rate at different times

Originally Posted by Kevin UK
Hi toxic27

How come one gets a 1.33 rate if you work after 24:00, but then you say that if one works before 08:30 you get a rate of 1.25!

See the attached file, you did not mention breaks or weekend working so I have omitted these.
Hi,

I would like to use the template you uploaded (289-Time Sheet Formula - different rate at different times.xlsx‎) but I need 30 min break deducted each day. The break is to be deducted from the highest number of hours worked that day e.g. if there's 2 hours at rate 1, 6hrs at rate 2, and 2hrs at rate 3. I need 30 mins deducted from "rate 2" hours.

Can you or anyone else help?

13. ## Re: Time Sheet Formula - different rate at different times

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1