Time sheet
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Time sheet

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

    Default

     
    I am kind of new with excel. I have developed a weekly time sheet. in the Cell H, I have it to where it totals per day hours, exe) 8:45(h.mm), I would like to add a cell I to automatically notice that after 8 hours to put in the overtime hours after 8 hours into cell I.

    Also, weekends are automatic overtime.

    Does anyone know what kind of funtion to put into to achieve this.

    Thanks, wish I had found this forum a couple of days ago, I was going nuts with a certain funtion and took me 2 days to figure it out!

    [ This Message was edited by: Mitch on 2002-02-23 14:38 ]

    [ This Message was edited by: Mitch on 2002-02-23 15:01 ]

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

    Default

    The link asks for username & password. What is up?

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

    Default

    I am not sure, I re-set permission on the file, cleared my cache and didn't get a password prompt.

    Try it again and if there is a prompt, just hit ok and if it doesn't show up, let me know so I can track down why it is doing that.

    Thanks
    Mitch

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    First all my friends check your private messages from me

    Hi Mate not to happy hear i have gone to your link and some download went and my virus checker or should i say CHECKERS tripped and went MAD !

    Whats happening... Aladin report passwords i report VIRUS and sugget no one touches the link..

    Please explain poss this post needs removing otherwise..

    Rdgs

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

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

    Default

    I don't know what you are saying when you say this contains a virus, I just put this up and I don't rum macros or anything. I took the link off and checked it with my virus checker and did not receive anything, I am not sure why you are receiving an error, oh well, just put up to let someone see what I was trying to do, I don't want anyone getting the intention that I am putting viruses out there.

    Thanks
    Mitch

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

    Default

    =MIN(H17,1+TIME(40,0,0)) . This formula, which assumes a 40-hour week, returns the smaller of two values: the total number of hours worked, or 40 hours. This formula subtracts the number of regular hours from total hours to yield the number of overtime hours.

    I want this to work after 8 hours, does anyone know how I can impliment this.

    Thanks

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,827
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-02-23 15:28, Mitch wrote:
    =MIN(H17,1+TIME(40,0,0)) . This formula, which assumes a 40-hour week, returns the smaller of two values: the total number of hours worked, or 40 hours. This formula subtracts the number of regular hours from total hours to yield the number of overtime hours.

    I want this to work after 8 hours, does anyone know how I can impliment this.

    Thanks
    Mitch,

    I forgat a bit the lay-out of your worksheet.

    In I3 enter (I think it was row 3 where your data for regular days start):

    =IF(AND(H3,H3>"8:00"+0),H3-"8:00"+0,0)

    and copy down.

    Note. The above is not meant for Saturday and Sunday entries.

    I also noticed that you check whether there is an entry before you do a substraction. A good thing to do. However, you can shorten the formulas that do such checking like in

    =IF(A1,B1-A1,0)

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-02-23 23:18 ]

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

    Default

    Ok, It didn't work. Let me explain better!

    Here is a sample layout:

    A17
    Monday

    b17 (IN)
    7:30 A

    C17 (OUT)
    11:30 A

    D17 (TOTAL HOURS)
    4:00

    E17 (IN)
    12:30 P

    F17 (OUT)
    5:30 P

    G17 (TOTAL HOURS)
    5:00

    H17 (TOTAL REGULAR HOURD)
    9:00

    I17 (OVERTIME HOURS)
    ------>THIS IS WHERE I NEED TO FIGURE WHAT FUNCTION TO PUT THAT WILL ADD THE 1 HOUR OVERTIME

    Hope this explaines better, thanks
    Mitch

    [ This Message was edited by: Mitch on 2002-02-23 17:14 ]

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The following are the sample formulas for your cells.

    C17
    =Sum(C17,-B17)

    F17
    =SUM(F17,E17)

    G17
    =SUM(C17,F17)

    for the rows following you can just use auto fill(select A17:I17 and drag to row 18 and following rows needed)

    Hope this helps you

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,827
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    On 2002-02-23 17:06, Mitch wrote:
    Ok, It didn't work. Let me explain better!

    Here is a sample layout:

    A17
    Monday

    b17 (IN)
    7:30 A

    C17 (OUT)
    11:30 A

    D17 (TOTAL HOURS)
    4:00

    E17 (IN)
    12:30 P

    F17 (OUT)
    5:30 P

    G17 (TOTAL HOURS)
    5:00

    H17 (TOTAL REGULAR HOURD)
    9:00

    I17 (OVERTIME HOURS)
    ------>THIS IS WHERE I NEED TO FIGURE WHAT FUNCTION TO PUT THAT WILL ADD THE 1 HOUR OVERTIME

    Hope this explaines better, thanks
    Mitch

    [ This Message was edited by: Mitch on 2002-02-23 17:14 ]
    In I17 enter:

    IF(AND(H17,H17>"8:00"+0),H17-"8:00"+0,0)

    Custom format I17 as [hh]:mm


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
  •  

 

 
DMCA.com