Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: calculating time with 1/4, 1/2/ and 3/4 of an hour increments

  1. #1
    New Member
    Join Date
    Jan 2006
    Location
    Snellville, GA
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default calculating time with 1/4, 1/2/ and 3/4 of an hour increments

    Hello,

    Please help!!!
    We have a client who will pay using the 1/4, 1/2/ and 3/4 of an hour increments as follows:

    From the hour mark to 6 minutes = 0 time paid
    Over 7 minutes to 15 minutes = 1/4 hour 15
    16 minutes to 30 minutes = 1/2 hour 30
    30 minutes to 45 minutes = 3/4 hour 45
    46 minutes to 60 minutes = one hour.

    They work on shifts and the operation is 24 hours. Each gets 30 minutes for breaks which is not paid. Pay is biweekly and overtime is anything over 40 hours. Currently, the template we are using looks like this (sorry, I could not figure out how to paste it so you can see it in Excel layout):
    WEEK 1 Sunday Monday Tuesday Wednesday Thursday Friday Saturday
    5/16 5/17 5/18 5/19 5/20 5/21 5/22
    TIME IN ENTER TIME, INCLUDE AM OR PM
    TIME OUT
    TIME IN
    TIME OUT
    TIME IN
    TIME OUT
    TIME IN Total Hours First Week
    TIME OUT
    HOURS WORKED-OR- choose one
    *HOLIDAY WORKED
    PAID HOLIDAY (not worked)
    *OTHER PAID HOURS
    EXPLANATION OF *HOLIDAY WORKED HRS OR *OTHER

    I am very new to Excel and need help creating a formula that will allow us to calculate this timesheet? I am in desperate need for help.

    Thanks..

  2. #2
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,584
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: calculating time with 1/4, 1/2/ and 3/4 of an hour increments

    Is the time rounded from the total or each individual time in/out?

    The basic formula for rounding time to the 1/4 hour is this:

    =ROUND(A1*96,0)/96
    Office 2010/365

  3. #3
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    20,972
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: calculating time with 1/4, 1/2/ and 3/4 of an hour increments

    Are those amounts calculated for each interval, or for each day? If for the day, then

    Code:
          -A- ---B----
      1       Mon 5/16
      2   In      7:30
      3   Out    10:45
      4   In     11:03
      5   Out    13:51
      6   In     14:12
      7   Out    16:18
      8           8:15
    The formula in B8 is

    =FLOOR(SUMPRODUCT(B2:B7, -1^(ROW(B2:B7) - ROW(B1))), "1:00")
    + LOOKUP(MOD(SUMPRODUCT(B2:B7, -1^(ROW(B2:B7) - ROW(B1))), "1:00"), {0,7,16,30,45}/1440, {0,1,2,3,4}/96)

  4. #4
    New Member
    Join Date
    Jan 2006
    Location
    Snellville, GA
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: calculating time with 1/4, 1/2/ and 3/4 of an hour increments

    Shq,

    OMG, that is brilliant!!! It worked except in cases where the employee's shift is overnight - say starting 21:00 pm and ending 7:00 am. In those cases I get #NUM! error.

    Also, the daily totals has now changed as well. Daily totals = B8 (solution above) + values in some or none of the cells below:
    *HOLIDAY WORKED
    PAID HOLIDAY (not worked)
    *OTHER PAID HOURS
    PTO (VACATION)
    FAMILY SICK LEAVE
    DAILY TOTALS

    For instance, using the example from above, the daily totals now reads: 7:12 eventhough only B8 has a value of 8:15 in it. Not sure why. I tested using another where C8 is 9:00 - the daily totals reads 9:36. Not sure why.

    I hope that I did not confuse you too much. Please let me if I can clarify anyting.

    I am grateful for your assistance and appreciate any more help you can give. This will surely help to minimize some frustrations for me.

    Again, thanks.

    Dalanda

  5. #5
    New Member
    Join Date
    Jan 2006
    Location
    Snellville, GA
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Re: calculating time with 1/4, 1/2/ and 3/4 of an hour increments

    Quote Originally Posted by HOTPEPPER View Post
    Is the time rounded from the total or each individual time in/out?

    The basic formula for rounding time to the 1/4 hour is this:

    =ROUND(A1*96,0)/96
    Thanks HOTPEPPER. I really appreciate you taking the time to respond to my post. In this case, Shq solution worked out better.

    Dalanda

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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