+ Reply to Thread
Results 1 to 8 of 8

WEEKENDS VS. WEEKDAYS

  1. #1
    TLAngelo
    Guest

    WEEKENDS VS. WEEKDAYS

    I have created a spreadsheet that calculates process times. If a product is
    held overnight, then excel subtracts 14 hours from the total to account for
    14 hours that are non-working hours, if a product is held over a weekend,
    then excel subtracts 62 hours of non-working time (24x2 for Sat. & Sun. +14
    for Fri. night). The problem I am running into is that if a product is held
    for 1 or 2 days and those days are NOT weekends, then I only want it to
    subtract the 14 hours for however many days.

    Also, can you have separate formulas in a cell without nesting them? For
    example, an "OR" formula?



  2. #2
    jim
    Guest

    Re: WEEKENDS VS. WEEKDAYS

    To your first question, how about doing something like
    =IF(WEEKDAY(A2,2)>5,X-14,X-62). WEEKDAY assigns a numeric value,
    starting with Monday (with the ",2"). So if it's greater than 5, it's
    a weekend (6=SAT and 7=SUN). In the above example, X is equal to the
    number of days held. If you want to account for a mix of weekdays and
    weekends you might need to get a little more complex, but this should
    serve as a start.

    I don't think there's an OR operator in Excel -- I've always embedded
    my conditionals.


  3. #3
    Bob Phillips
    Guest

    Re: WEEKENDS VS. WEEKDAYS

    What happens if one of the dates is Sat or Sun, is this possible? Can it
    span multiple weekends?

    --
    HTH

    Bob Phillips

    (remove xxx from email address if mailing direct)

    "TLAngelo" <[email protected]> wrote in message
    news:[email protected]...
    > I have created a spreadsheet that calculates process times. If a product

    is
    > held overnight, then excel subtracts 14 hours from the total to account

    for
    > 14 hours that are non-working hours, if a product is held over a weekend,
    > then excel subtracts 62 hours of non-working time (24x2 for Sat. & Sun.

    +14
    > for Fri. night). The problem I am running into is that if a product is

    held
    > for 1 or 2 days and those days are NOT weekends, then I only want it to
    > subtract the 14 hours for however many days.
    >
    > Also, can you have separate formulas in a cell without nesting them? For
    > example, an "OR" formula?
    >
    >




  4. #4
    jim
    Guest

    Re: WEEKENDS VS. WEEKDAYS

    WEEKDAY should always return a value between 1 and 7. So weekends will
    be 6 and 7 (Sat and Sun) if you use the formula above. If you count
    anything coded 1-5 as a weekday (14hrs) and anything greater than 5 as
    a weekend (24hrs) you should be okay.

    Again, if you're looking at counting multiple days you'll need to take
    this a little further, but hopefully this will get you started in
    determining whether a given date is a weekday or weekend.


    Jim


  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Assuming you have a process start time and date in A2 and a process end time and date in B2 and that these dates/times will always be within your working hours you could use the following formula to calculate the total process time

    =(NETWORKDAYS(A2,B2)-1)/2.4+MOD(B2,1)-MOD(A2,1)

    format as [h]:mm

    note that NETWORKDAYS is part of Analysis ToolPak add-in

  6. #6
    TLAngelo
    Guest

    Re: WEEKENDS VS. WEEKDAYS

    No, one of the dates will not be a Sat. or Sun. It shouldn't span multiple
    weekends. My dates and times "in" are in fields D & E and dates and times
    "out" are in fields F & G. So normally, if a product is held for 2 or 3
    days, it is because there was a weekend, but sometimes a product may be held
    for 2 or 3 days and it is in the middle of the week (that's not good). If it
    was a weekend, I want to subtract the whole 24 hours of the days from the
    process time, if it was held for that long and it was during the week
    however, I only want to subtract the 14 hours of evening non-working time. I
    can write the formula to subtract one or the other, but I don't know how to
    tell excel to recognize if the holding time includes a weekend or not... is
    this clear as mudd??!!

    "Bob Phillips" wrote:

    > What happens if one of the dates is Sat or Sun, is this possible? Can it
    > span multiple weekends?
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (remove xxx from email address if mailing direct)
    >
    > "TLAngelo" <[email protected]> wrote in message
    > news:[email protected]...
    > > I have created a spreadsheet that calculates process times. If a product

    > is
    > > held overnight, then excel subtracts 14 hours from the total to account

    > for
    > > 14 hours that are non-working hours, if a product is held over a weekend,
    > > then excel subtracts 62 hours of non-working time (24x2 for Sat. & Sun.

    > +14
    > > for Fri. night). The problem I am running into is that if a product is

    > held
    > > for 1 or 2 days and those days are NOT weekends, then I only want it to
    > > subtract the 14 hours for however many days.
    > >
    > > Also, can you have separate formulas in a cell without nesting them? For
    > > example, an "OR" formula?
    > >
    > >

    >
    >
    >


  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    I think you could use the formula I suggested modified to

    =(NETWORKDAYS(D2,F2)-1)/2.4+G2-E2

    format as [h]:mm

    This will calculate the working hours between your in and out times, based on a 10 hour day, assuming that in and out times are always within working hours. It will take weekends into account and can also be amended to cope with holidays if you want to do that.

    If you want to stick with your original method I imagine you could identify when a weekend has intervened between in date and out date because this would be the only situation where WEEKDAY(F2) would be less than WEEKDAY(D2)

  8. #8
    TLAngelo
    Guest

    Re: WEEKENDS VS. WEEKDAYS

    PERFECT! Thanks so much!

    "daddylonglegs" wrote:

    >
    > I think you could use the formula I suggested modified to
    >
    > =(NETWORKDAYS(D2,F2)-1)/2.4+G2-E2
    >
    > format as [h]:mm
    >
    > This will calculate the working hours between your in and out times,
    > based on a 10 hour day, assuming that in and out times are always
    > within working hours. It will take weekends into account and can also
    > be amended to cope with holidays if you want to do that.
    >
    > If you want to stick with your original method I imagine you could
    > identify when a weekend has intervened between in date and out date
    > because this would be the only situation where WEEKDAY(F2) would be
    > less than WEEKDAY(D2)
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
    > View this thread: http://www.excelforum.com/showthread...hreadid=539083
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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