Add days, excluding Sunday's and Holiday's

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

 

[Bernard Liengme]

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

 

[Mike H]

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

 

[Shane Devenshire]

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 Otten]

Hi Mike,

What's "Days" referencing?

 

[Niek Otten]

What you could do is use Chip Pearson's betternetworkdays Function and use
Goal Seek to get 7 as the number of workdays
Look here:

http://www.cpearson.com/excel/betternetworkdays.aspx

 

[Mike H]

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

 

[JoeL]

This is great, but it doesn't include Saturdays. Can we modify it to include
this day too?

 

[JoeL]

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.

 

[JoeL]

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.

 

[Niek Otten]

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

 

[Mike H]

I assumed you would know that

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

 

[Niek Otten]

Instead of ENTER, use CTRL+SHIFT+ENTER to commit the formula

 

[JoeL]

I didn't know that... but now this formula works great. Thank you!

 

[Mike H]

Your welcome and I should have pointed out it was an array

 

[sanjibkrjana]

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