+ Reply to Thread
Results 1 to 6 of 6

formula for elapsed time with condition

Hybrid View

  1. #1
    Registered User
    Join Date
    11-08-2011
    Location
    Yakima, Washington
    MS-Off Ver
    Excel 2007
    Posts
    3

    Question formula for elapsed time with condition

    Hi - I need help on a forumla for calculating hours worked with 1/2 hour lunch/break automatically subtracted. For every 5 hours of work, a 1/2 hour break needs to be subtracted. I'm thinking that would be >=5.5 hours is the key time.

    A9 is TIME IN and B9 is TIME OUT.

    The formula that I'm using is this: =IF(B9-A9>=0.2291685,B9-A9-0.0208335,B9-A9)*24

    This formula works for hours worked up to and including 5 as well as over 5.5, but when an ee works 5.5 hours, it should subtract that 1/2 hour and return 5 hours, but I can't get it to do that. I have tried moving around the conditons with no success. I hope I've been clear. Thanks in advance for any help.

  2. #2
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: formula for elapsed time with condition

    How about this: (I used fractions instead of decimals for accuracy/ease of use)

    =IF(B9-A9>=5.5*(1/24),B9-A9-(1/48),B9-A9)*24
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave

  3. #3
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: formula for elapsed time with condition

    Do you mean =IF((B9-A9)*24>=5.50,(B9-A9)*24-0.5,(B9-A9)*24) ?

  4. #4
    Registered User
    Join Date
    11-08-2011
    Location
    Yakima, Washington
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: formula for elapsed time with condition

    Yea! thank you so much - both of those formulas work well, except for the 2nd 5.5 hour period, an additional 1/2 is not subtracted. We have ee's that sometimes work over 11 and 12 hours during harvest, so it's necessary to have a full hour subtracted if they work over 11 hours.

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,695

    Re: formula for elapsed time with condition

    This formula will deduct 30 minutes for every 5.5 hours

    =(B9-A9)*24-INT((B9-A9)*24/5.5)/2

    format result cell as number
    Audere est facere

  6. #6
    Registered User
    Join Date
    11-08-2011
    Location
    Yakima, Washington
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: formula for elapsed time with condition

    Thank you, daddylonglegs. This will save me considerable time and effort. Blessings upon you and your day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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