Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Time sheet

  1. #11
    New Member
    Join Date
    Feb 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Aladin Akyurek, That did the trick!

    There is one more thing and the time card is complete! and will be ready to utilize for Monday's start time and make my life much easier for the 23 employee's I have to track.

    In H17 (Total Regular Hours) I would like it to add G17 and D17 and if the amount is less than [h.mm]8:00 put that amount in H17 (eg: 7:43) and if it is greater than [h.mm]8:00, just put [h.mm]8:00 in H17, because the overtime hours are already put in I 17.

    Thanks alot!
    Mitch

  2. #12
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-24 05:47, Mitch wrote:
    That did the trick!

    There is one more thing and the time card is complete! and will be ready to utilize for Monday's start time and make my life much easier for the 23 employee's I have to track.

    In H17 (Total Regular Hours) I would like it to add G17 and D17 and if the amount is less than [h.mm]8:00 put that amount in H17 (eg: 7:43) and if it is greater than [h.mm]8:00, just put [h.mm]8:00 in H17, because the overtime hours are already put in I 17.
    Mitch,

    Your last request would give us a circular reference. That's why I asked you sending me your workbook in order to start over. Don't be alarmed!

    Here is how your worksheet for Weekly Time Record looks like including everything:

    {"Day","In","Out","Total Time","In","Out","Total Time","Regular Hours","Overtime";
    "Saturday",0.291666666666667,0.458333333333333,0.166666666666667,0,0,0,0,0.166666666666667;
    "Sunday",0,0,0,0,0,0,0,0;
    "Monday",0.270833333333333,0.458333333333333,0.1875,0.583333333333333,0.708333333333333,0.125,0.3125,0;
    "Tuesday",0.270833333333333,0.458333333333333,0.1875,0.583333333333333,0.738194444444444,0.154861111111111,0.333333333333333,0.00 902777777777769;
    "Wednesday",0.270833333333333,0.458333333333333,0.1875,0.583333333333333,0.708333333333333,0.125,0.3125,0;
    "Thursday",0.270833333333333,0.458333333333333,0.1875,0.583333333333333,0.708333333333333,0.125,0.3125,0;
    "Friday",0.291666666666667,0.5,0.208333333333333,0.583333333333333,0.708333333333333,0.125,0.333333333333333,0;
    0,0,0,0,0,0,"Total Hours",1.60416666666667,0.175694444444444}

    Note. Zeroes stand for empty cells and funny looking numbers are times so as they are internally represented by Excel.

    The above data along with calculations are in A9:I17.

    In D10 enter:

    =IF(AND(ISNUMBER(B10),ISNUMBER(C10)),C10+(C10
    [ copy down till the Friday row ]

    In G10 enter:

    =IF(AND(ISNUMBER(E10),ISNUMBER(F10)),F10+(F10
    [ copy down till the Friday row ]

    In H10 enter:

    =IF(OR(A10="Saturday",A10="Sunday"),0,IF(SUM(D10,G10)>"8:00"+0,"8:00"+0,SUM(D10,G10)))

    [ copy down until the Friday row ]

    In H11 enter: =SUM(H10:H16)

    In I10 enter:

    =IF(OR(A10="Saturday",A10="Sunday"),SUM(D10,G10),IF(SUM(D10,G10)>"8:00"+0,SUM(D10,G10)-"8:00"+0,0))

    [ copy down this till the Friday row ]

    Note 1. I had to change the earlier formula for I in order to avoid circular references which were bound to occur because of your latest request.

    Note 2. As you wanted, column H will show only totals of regular work hours at a max of 8:00 involving a day. Any overtime hours will be shown in I.

    In I11 enter: =SUM(I10:I16)

    All cells where time diffs and totals are calculated are custom formatted as [hh]:mm.

    I'll send you the modified workbook via e-mail.

    Aladin

  3. #13
    New Member
    Join Date
    Feb 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Your Amazing!! This works perfect and is just what I needed.

    Thanks for all your help

  4. #14
    New Member
    Join Date
    Feb 2004
    Location
    az
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Re: Time sheet

    I have this problem where my spreadsheet is calculating worked hours, regular hours, and overtime hours. Any hours over 8 automatically goes into overtime hours which is fine, but once you go over 40 hours, the hours still split between regular hours and overtime hours. I need the formulas to figure 40 hours in the work week, EVERYTHING else is OT.

    Is there any way for me to attach my spreadsheet?
    Last edited by miraclmoore; Dec 30th, 2010 at 01:36 PM.

  5. #15
    New Member
    Join Date
    Feb 2004
    Location
    az
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Time sheet

    Quote Originally Posted by miraclmoore View Post
    I have this problem where my spreadsheet is calculating worked hours, regular hours, and overtime hours. Any hours over 8 automatically goes into overtime hours which is fine, but once you go over 40 hours, the hours still split between regular hours and overtime hours. I need the formulas to figure 40 hours in the work week, EVERYTHING else is OT.

    Is there any way for me to attach my spreadsheet?
    Here is the link to my file:

    http://www.mediafire.com/?l64awr6h84m3rlo

    Any and all help would be greatly appreciated.

    Happy New Years!

  6. #16
    New Member
    Join Date
    Feb 2004
    Location
    az
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Re: Time sheet

    Is there anyone that can help me with this calculation?

Some videos you may like

User Tag List

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
  •