+ Reply to Thread
Results 1 to 9 of 9

Time Sheet Formula - different rate at different times

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

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,040

    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?
    Bernie Deitrick
    Excel MVP 2000-2010

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

    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.

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,040

    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

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

    Re: Time Sheet Formula - different rate at different times

    Hmm weird the formula gives an error.

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,040

    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.

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

  8. #8
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,040

    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
    Last edited by Bernie Deitrick; 07-24-2014 at 09:51 AM.

  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

    Awesome! This is what I mean, thanks a lot

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Time Sheet Formula - different rate at different times
    By toxic27 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-10-2020, 08:08 AM
  2. [SOLVED] Formula for Vacation Time Max Accrual Rate
    By brad.freeman in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-18-2013, 05:34 AM
  3. If a search term can be found 1 time, 2 times 3 times 4 times 5 times
    By excelcandy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-10-2013, 09:57 PM
  4. Time Sheet: Hourly Rate for work Performed between 10PM to 6AM
    By nicoskygush in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-06-2009, 03:45 PM
  5. [SOLVED] Calculation of hourly rate times hours times 1.5
    By Newbusinessbod in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-06-2005, 12:50 PM

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