Skip the holidays
 

Is there a way to skip the holidays in this formular:

=IF(E$23="","",IF(WEEKDAY(E$23+1,2)5,E$23+1+(WEEK DAY(E$23+1)2)*7-WEEKDAY(E$23+1)+2,E$23+1))

I have the days set up automatically but with this formula, the holidays are
still in.

Thank you,

Aviator

I did it by adding the NETWORKDAYS() function to the front of your IF()
function:
=IF(NETWORKDAYS(E$23,E$23,I2:I7)=1,IF(E$23="","",I F(WEEKDAY(E$23+1,2)5,E$23+1+(WEEKDAY(E$23+1)2)*7-WEEKDAY(E$23+1)+2,E$23+1)),"Weekend/Holiday")
.... where I2:I7 is a list of Holiday dates.

By the way, is this a syntax error in your formula?
WEEKDAY(E$23+1)2)*

Looks like the "+12" should be "+1,2"

I used the formula that you sent and I am getting an error. It is an "invalid
name error". It seems to have a problem with NETWORKDAYS.

I have it set up such that the person inputs a date and the rest of the
cells fill in. Right now, with the current fomula, I have it so only the
work week shows. I would also like to get rid of (skip) the holidays
automatically from the cell.

Thank you,

Aviator


"Dave O" wrote:

I did it by adding the NETWORKDAYS() function to the front of your IF()
function:
=IF(NETWORKDAYS(E$23,E$23,I2:I7)=1,IF(E$23="","",I F(WEEKDAY(E$23+1,2)5,E$23+1+(WEEKDAY(E$23+1)2)*7-WEEKDAY(E$23+1)+2,E$23+1)),"Weekend/Holiday")
.... where I2:I7 is a list of Holiday dates.

On your menu please click Tools, Add-Ins and make sure that Analysis
Tool Pak is selected. If it's not selected the NETWORKDAYS() function
returns an error.

When you say
I would also like to get rid of (skip) the holidays automatically
from the cell.
.... do you mean you want to list holidays in the formula itself?
That's possible, but a little chunky.

No, I don't want to list the holidays in the formula. I have a column with
all the holidays (AZ2:AZ25). But, for example, I will have a row of 10 cell.
I input the first date and the next 9 cells are supposed to show the next
working dates.

I input 12/22, the next cell shows 12/23, then 1/3 (the reason it should
skip or jump to 1/3 is because of the weekends and holidays).

I have added the Analysis Tool Pak. I am no longergetting the error message.
But, I am not getting the correct dated either (if there is a date). Plaese
help.

Thank you,

Aviator


"Dave O" wrote:

On your menu please click Tools, Add-Ins and make sure that Analysis
Tool Pak is selected. If it's not selected the NETWORKDAYS() function
returns an error.

When you say
I would also like to get rid of (skip) the holidays automatically
from the cell.
.... do you mean you want to list holidays in the formula itself?
That's possible, but a little chunky.

The following formula works well it just does not exclude the holidays.

Calendar Formula without Weekends (Col. E)
=IF(D$6="","",IF(WEEKDAY(D$6+1,2)4,D$6+1+(WEEKDAY (D$6+1)2)*7-WEEKDAY(D$6+1)+2,D$6+1))


I can not get this to work.

Calendar Formula without Weekends & Holidays (Col. E) (Does not Work)
=IF(NETWORKDAYS(D$23,D$23,AZ2:AZ27)=1,IF(D$23=""," ",IF(WEEKDAY(D$23+1,2)4,D$23+1+(WEEKDAY(D$23+1)2 )*7-WEEKDAY(D$23+1)+2,D$23+1)),"Weekend/Holiday")


The following is how I have things set up.

Formula:
1. Input date scheduled to start: 12/23
2.
=IF(D$40="","",IF(WEEKDAY(D$40+1,2)4,D$40+1+(WEEK DAY(D$40+1)2)*7-WEEKDAY(D$40+1)+2,D$40+1))
3.
=IF(E$40="","",IF(WEEKDAY(E$40+1,2)4,E$40+1+(WEEK DAY(E$40+1)2)*7-WEEKDAY(E$40+1)+2,E$40+1))
4.
=IF(F$40="","",IF(WEEKDAY(F$40+1,2)4,F$40+1+(WEEK DAY(F$40+1)2)*7-WEEKDAY(F$40+1)+2,F$40+1))

What I have currently:
1 2 3 4
Col. D Col. E Col. F Col. G
12/23 12/27 12/28 12/29


This is the way I want it to look like.

Wanting to Skip Weekends & Holidays:
1 2 3 4
Col. D Col. E Col. F Col. G
12/23 1/3 1/4 1/5

Formula:
1 Input date scheduled to start: 12/23
2 ?
3 ?
4 ?

Note: AZ2:AZ25 are the listed holidays
---------------------------------------------------------------------------------------------


"Dave O" wrote:

On your menu please click Tools, Add-Ins and make sure that Analysis
Tool Pak is selected. If it's not selected the NETWORKDAYS() function
returns an error.

When you say
I would also like to get rid of (skip) the holidays automatically
from the cell.
.... do you mean you want to list holidays in the formula itself?
That's possible, but a little chunky.