WEEKENDS VS. WEEKDAYS

[Guest]

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?

 

[jim]

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.

 

[Bob Phillips]

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)

 

[jim]

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

 

[daddylonglegs]

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

 

[Guest]

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??!!

 

[daddylonglegs]

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)

 

[Guest]

PERFECT! Thanks so much!