Skip the Holidays 2

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


Please Help! Thank you,

Aviator

 

[Bob Phillips]

Aviator,

Why not just WORKDAY

=IF(D$6="","",workday(D6,1,holidays))

where holidays is the holidays range

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Aviator]

This works great. The only problem is that we only work Monday thru Thusday.
That is why I was using:

=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))

Is there a way to accomplish both 4 day work weeks and skipping the holidays?

Thank you,

Aviator

 

[Bob Phillips]

Aviator,

It can be done with formulae, but they can be very complex. I prefer to have
a UDF that does it. This is such a UDF that I have adapted from one of my
date UDFs. You would use it like so

=fWORKDAY(D$5,1,holidays,FALSE)

The last FALSE is important otherwise it just works like a normal WORKDAY
function.

'---------------------------------------------------------------------
Function fWORKDAY(StartDate As Variant, _
Days As Variant, _
Optional Holidays, _
Optional IncFri As Boolean = True)

'---------------------------------------------------------------------
Dim cDays As Long, cDays2 As Long
Dim StartDateWe As Date, EndDate As Date, EndDateWE As Date

Application.Volatile

'check if valid arguments
If (Not IsDate(StartDate)) Then
GoTo DF_errValue_exit
ElseIf (Not IsNumeric(Days)) Then
GoTo DF_errValue_exit
ElseIf Days < 1 Or IsEmpty(Days) Then
GoTo DF_errValue_exit
ElseIf (Not IsMissing(Holidays)) Then
If (TypeName(Holidays) <> "Range" And _
TypeName(Holidays) <> "String()" And _
TypeName(Holidays) <> "Variant()") Then
GoTo DF_errValue_exit
End If
End If

#If fDebug Then
Debug.Print StartDate & ", " & _
Days & ", " & _
IncSat & ", " & _
IncSun
#End If

EndDate = Application.Run("ATPVBAEN.XLA!WORKDAY", StartDate, Days,
Holidays)

'add extra days to cover fridays
'determine the friday after end date
If (Not IncFri) Then
cDays2 = IIf(Weekday(StartDate, vbSunday) = vbFriday, 6, 7)
EndDateWE = EndDate + (cDays2 - Weekday(EndDate, vbSunday))
cDays = cDays + ((EndDateWE - (StartDate + 1)) \ 7)
If Weekday(EndDate, vbSunday) = vbFriday Then cDays = cDays + 1
End If

If (cDays > 0) Then
fReEntry = True
EndDate = fWORKDAY(StartDate:=EndDate, _
Days:=cDays, _
Holidays:=Holidays, _
IncFri:=IncFri)
End If

fWORKDAY = EndDate

fReEntry = False

Exit Function

DF_errValue_exit:
fWORKDAY = CVErr(xlErrValue)

End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Aviator]

I am not sure what UDF is. Were would I put this information?

 

[Bob Phillips]

A UDF is a Used Defined Function. This is VBA, so it goes into a standard
code module. Go into the VBE (Alt-F11), insert a code module (menu
Insert>Module), and paste that code into it.

You just use it as any other worksheet function.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Aviator]

I must be missing something. I have done as you said and all I get is Name
Errors.
Do you have any other ideas?

Thank you,

Aviator

 

[Bob Phillips]

#Name means it cannot find the UDF, thus it seems that you have done
something incorrectly. Without seeing what you have done, I am stumped.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ron Rosenfeld]

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


Please Help! Thank you,

If I understand you correctly, you wish to generate a list of successive dates
which excludes Fridays, Saturdays, Sundays and holidays.

If you have the Analysis Tool Pak installed, you can use a slight modification
of the Workday function:

=workday(D40+(WEEKDAY(D40)=5),1,Holidays)

where Holidays is the range where you have stored a list of holidays.

You can, of course, "wrap" this in your IF statement:

=IF(D40="","",workday(D40+(WEEKDAY(D4)=5),1,Holidays))


--ron

 

[Ron Rosenfeld]

If I understand you correctly, you wish to generate a list of successive dates
which excludes Fridays, Saturdays, Sundays and holidays.

If you have the Analysis Tool Pak installed, you can use a slight modification
of the Workday function:

=workday(D40+(WEEKDAY(D40)=5),1,Holidays)

where Holidays is the range where you have stored a list of holidays.

You can, of course, "wrap" this in your IF statement:

=IF(D40="","",workday(D40+(WEEKDAY(D4)=5),1,Holidays))


--ron

TYPO Alert: with the IF, should read:

=IF(D40="","",workday(D40+(WEEKDAY(D40)=5),1,Holidays))


--ron