Add days, excluding Sunday's and Holiday's

  • Thread starter Thread starter JoeL
  • Start date Start date
J

JoeL

I have a process that requires 7 days to complete. So, I've been asked to add
7 days to a manual inputted date (cell c1), to include Saturday's, but not
Sunday's or holiday's.

How can I do this?

Please help or if you need clarification.

Thank you!
 
Have a look at the WORKDAY function
Example A1 holds today's date (July 8), B1 holds number 5
WORKDAY(A1,B1) returns July 13 (Wed to Wed)
You may need to format the cell holding the formula if it returns a numbers
like 40009
best wishes
 
Hi,

Try this courtesy of Bob Philips

=A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})*
ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(Days)*10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))),ABS(7))

Start date in a1 and a named range called 'Holidays' for any holidays you
want to excelude. Format as date.

Mike
 
Hi,

I think this is a lot more complicated than a simple WORKDAY function,
because of the exclusing of SUNDAYs but not SATURDAYS. The holiday's part is
easy.

If the process is only 7 days or less then

=WORKDAY(A1,A2+1,C1:C5)

If not ...

Also, the WORKDAY function is an ATP function so in 2003 or earlier you will
need to attach it - Tools, Add-ins, check Analysis ToolPak.
 
Niek,

I messed up days is a named range containg the days to add, I changed it in
the first part of the formula to 7 (the days to add) but forgot in the second
but because I had that named range in my sheet it worked for me. It should be

=A1+SIGN(7)*SMALL(IF((WEEKDAY(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(7)*10))))={2,3,4,5,6,7})*
ISNA(MATCH(A1+SIGN(7)*(ROW(INDIRECT("1:"&ABS(7)*10))),Holidays,0)),ROW(INDIRECT("1:"&ABS(7)*10))),ABS(7))

Since then i've been working on a shorter version but cant get it into 1
cell (yet)

A1 =start date
A2 = =WORKDAY(A1,7,Holidays)
A3 = =A2-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=7))

It works but as I said can't get it into a single cell

Mike

Mike
 
This is great, but it doesn't include Saturdays. Can we modify it to include
this day too?
 
Wow, this is extensive. I didn't get it to work for me though. I changed the
A1's to C7 (input date) and created a list of holidays (named Holidays and
formatted to date). However, I keep getting the #NUM! error.
 
This didn't work for me. It is only 7 days or less, but what are you assuming
is in cells A1, A2 and C1 through C5? Can you break this down for me and
maybe I can piece together? Thanks.
 
Yes, Your long formula works! It has to be array-entered.
(That is, instead of committing with ENTER, commit with CTRL-SHIFT-ENTER)
 
I assumed you would know that

Niek Otten said:
Yes, Your long formula works! It has to be array-entered.
(That is, instead of committing with ENTER, commit with CTRL-SHIFT-ENTER)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel
Click to expand...
 
Can any body please please upload the excel file?

Because I am getting error message every time I press the ENTER or CRTL+SHIFT+ENTER.

I need the formula urgently

:cry:
 
Back
Top