F
Frank Pytel
I need to calculate the number of net work days between two input fields that
hold dates. The net work days may include saturdays or sundays. I have a
holidays array for exclusion.
I am using =SUMPRODUCT(--(holidays>=$B$5),--(holidays<=$B$6),holc) to
calculate the holidays and networkdays(a,b) to calculate the net work days
and then subtracting. I use the holidays elswhere so I need them broken out.
Sometimes I want saturday and or sunday included. Sometimes weekdays are
excluded. I have set up an array that shows the following.
QualifierD Name Start End QualifierOpen
1 Mon 7 7 True (1)
2 Tue 7 7 True (1)
3 Wed 7 7 True (1)
4 Thur False (0)
5 Fri 7 7 True (1)
6 Sat 7 7 True (1)
7 Sun False (0)
The QualifierD is to use conditional formatting to alert me when a holiday
falls on a Tuesday or a Thursday. The QualifierOpen is tied to the days of
the week that the business is open for.
What I would like to do is set this up so that if the qualifier has a 1 or
true return calculation, then the day would be counted within the period I
want to pull the report for. If it is false it would not be counted. This
file will only cover a one year time span. 1/1 - 12/31. Then I would copy and
paste the application and start a new file, possibly with different hours.
Can anyone direct me as to how to write this function.
Thanks
Frank Pytel
hold dates. The net work days may include saturdays or sundays. I have a
holidays array for exclusion.
I am using =SUMPRODUCT(--(holidays>=$B$5),--(holidays<=$B$6),holc) to
calculate the holidays and networkdays(a,b) to calculate the net work days
and then subtracting. I use the holidays elswhere so I need them broken out.
Sometimes I want saturday and or sunday included. Sometimes weekdays are
excluded. I have set up an array that shows the following.
QualifierD Name Start End QualifierOpen
1 Mon 7 7 True (1)
2 Tue 7 7 True (1)
3 Wed 7 7 True (1)
4 Thur False (0)
5 Fri 7 7 True (1)
6 Sat 7 7 True (1)
7 Sun False (0)
The QualifierD is to use conditional formatting to alert me when a holiday
falls on a Tuesday or a Thursday. The QualifierOpen is tied to the days of
the week that the business is open for.
What I would like to do is set this up so that if the qualifier has a 1 or
true return calculation, then the day would be counted within the period I
want to pull the report for. If it is false it would not be counted. This
file will only cover a one year time span. 1/1 - 12/31. Then I would copy and
paste the application and start a new file, possibly with different hours.
Can anyone direct me as to how to write this function.
Thanks
Frank Pytel