Results 1 to 6 of 6

Thread: need help with my formula

  1. #1

    need help with my formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    hi guys don't know if I posted in the correct place I need help with my formula its for a time sheet if I enter in 16:00 and out 01:00 it shows me -15 hours where it should be 7 and then 2 hours ovetime, please I tried everything and cant fix this
    Attached Files Attached Files

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    769
    Articles
    0
    Excel Version
    2010
    Hello
    The problem is that your only showing the time in your "in" and "out" cols. This works OK until the two times are in different days. Change the content in these cells to include the date as well, and you will get the correct calculation.
    You can still format the cells to display the time only if you wish.

  3. #3
    this did not work my hours can only be 7 any hours more then 7 should go to the ovetime tab

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Without changing anything you already have on your worksheet, try this and drag down

    G5 =IF(ABS(($F5)-($C5)+(($C5)>($F5)))*24<=7,ABS(($F5)-($C5)+(($C5)>($F5)))*24,7)

    H5 =IF(ABS(($F5)-($C5)+(($C5)>($F5)))*24>7,(ABS(($F5)-($C5)+(($C5)>($F5)))*24)-7,0)

  5. #5

    thank ye that worked

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    769
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by arne View Post
    this did not work my hours can only be 7 any hours more then 7 should go to the ovetime tab
    I know that you have a solution now, but I wanted to show that my suggestion also works. If you look at the cells Ive coloured red and green, the red does not have a fully specified date and time producing the erroneous difference of -15.
    The green has the fully specified date and time, and shows the correct figure of 7 with 2 hours overtime.
    Attached Files Attached Files

Posting Permissions

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