+ Reply to Thread
Results 1 to 13 of 13

Time Sheet Formula - different rate at different times

  1. #1
    Registered User
    Join Date
    06-05-2012
    Location
    Kevin Watson
    MS-Off Ver
    Excel 2011 Mac
    Posts
    4

    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. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    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?
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    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. #4
    Registered User
    Join Date
    06-05-2012
    Location
    Kevin Watson
    MS-Off Ver
    Excel 2011 Mac
    Posts
    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. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Time Sheet Formula - different rate at different times

    I read your question as:

    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. #6
    Registered User
    Join Date
    06-05-2012
    Location
    Kevin Watson
    MS-Off Ver
    Excel 2011 Mac
    Posts
    4

    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. #7
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Time Sheet Formula - different rate at different times

    Hi toxic27

    I am not sure about your rules!
    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.
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  8. #8
    Registered User
    Join Date
    06-05-2012
    Location
    Kevin Watson
    MS-Off Ver
    Excel 2011 Mac
    Posts
    4

    Re: Time Sheet Formula - different rate at different times

    thank you for you help this is just what a wanted

    thank you

  9. #9
    Registered User
    Join Date
    07-18-2014
    Location
    NL
    MS-Off Ver
    2007
    Posts
    7

    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. #10
    Registered User
    Join Date
    07-18-2014
    Location
    NL
    MS-Off Ver
    2007
    Posts
    7

    Re: Time Sheet Formula - different rate at different times

    Quote Originally Posted by ZeRoo_ View Post
    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. #11
    Registered User
    Join Date
    04-09-2020
    Location
    Scotland
    MS-Off Ver
    2013
    Posts
    2

    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?

    Many thanks in advance

  12. #12
    Registered User
    Join Date
    04-09-2020
    Location
    Scotland
    MS-Off Ver
    2013
    Posts
    2

    Re: Time Sheet Formula - different rate at different times

    Quote Originally Posted by Kevin UK View Post
    Hi toxic27

    I am not sure about your rules!


    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?

    Many thanks in advance

  13. #13
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Time Sheet Formula - different rate at different times

    Administrative Note:

    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.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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