Workday not including Saturdays

[jeff07kp]

Hi guys,

Well this is my problem. I work scheduling trucks and I have the following problem.

I need to calculate arrival times for merchandise. So if the truck arrives lets say NOV 21st in three different cells would calculate NOV20, nov19 and nov 18.

The problem now is that I can not count Sundays, nor holidays. How can I apply a formula where it would automatically Affect the other cells.

I have something like this

=WORKDAY(AC11,-1,$H$1:$H$10)

The problem with the formula above is that it deducts saturday. Saturday is a working day for me too. How can I correct this?

to make it easier I utilized three cells to calculate then, i use another formula to extract the info such as

=TEXT(AH20,"ddd")

any ideas anyone?

<!-- / icon and title --> <!-- message -->

<!-- / message --> <!-- edit note -->

 

[xld]

Try this array formula

=H1-SMALL(IF((WEEKDAY(H1-(ROW(INDIRECT("1:10"))))={2,3,4,5,6,7})*
ISNA(MATCH(H1-(ROW(INDIRECT("1:10"))),holidays,0)),ROW(INDIRECT("1:10"))),1)

 

[barry houdini]

If you only want to go back 1 day at a time, e.g. to get the previous workday from A2 you could perhaps use this formula which doesn't need to be array entered, assuming start date in A2

=A2-MIN(IF(WEEKDAY(A2-{1,2,3,4,5})<>1,IF(ISNA(MATCH(A2-{1,2,3,4,5},$H$1:$H$10,0)),{1,2,3,4,5})))

put that formula in A3 and copy down and you get each successive workday, going backwards, assuming you never have as many as 5 non-workdays in succession