Excel formulas

  • Thread starter Thread starter aviationmech
  • Start date Start date
A

aviationmech

I am using a workday fomula to fill in dates across my spreadsheet. The
formula also removes holidays. I need to include Saturdays as a workday. How
can I accomplish this?
 
=B1+SMALL(IF((WEEKDAY(B1+(ROW(INDIRECT("1:"&10))))={2,3,4,5,6,7})*
ISNA(MATCH(B1+(ROW(INDIRECT("1:"&10))),holidays,0)),ROW(INDIRECT("1:"&10))),1)

this is an arry formula, so commit with Ctrl-Shift-Enter
 
Bob Phillips said:
=B1+SMALL(IF((WEEKDAY(B1+(ROW(INDIRECT("1:"&10))))={2,3,4,5,6,7})*
ISNA(MATCH(B1+ROW(INDIRECT("1:"&10))),holidays,0)),
ROW(INDIRECT("1:"&10))),1)

this is an arry formula, so commit with Ctrl-Shift-Enter
Click to expand...
....

Or

=B1+MATCH(1,(WEEKDAY(B1+{1,2,3,4,5,6,7},2)<7)
*(COUNTIF(Holidays,B1+{1,2,3,4,5,6,7})=0),0)

which doesn't require array formula entry.
 
Back
Top