Skip the holidays

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

=IF(E$23="","",IF(WEEKDAY(E$23+1,2)>5,E$23+1+(WEEKDAY(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="","",IF(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 "+1>2" 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
 
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 sayfrom 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
 
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+(WEEKDAY(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+(WEEKDAY(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+(WEEKDAY(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
 
Back
Top