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+(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


Please Help! Thank you,

Aviator

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" wrote in message
...
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+(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


Please Help! Thank you,

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" wrote:

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" wrote in message
...
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+(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


Please Help! Thank you,

Aviator

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" wrote in message
...
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" wrote:

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" wrote in message
...
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+(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


Please Help! Thank you,

Aviator

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



"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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+(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


Please Help! Thank you,

Aviator

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
InsertModule), 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" wrote in message
...
I am not sure what UDF is. Were would I put this information?



"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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+(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


Please Help! Thank you,

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" wrote:

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
InsertModule), 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" wrote in message
...
I am not sure what UDF is. Were would I put this information?



"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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+(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


Please Help! Thank you,

Aviator

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


"Aviator" wrote in message
...
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" wrote:

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
InsertModule), 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" wrote in message
...
I am not sure what UDF is. Were would I put this information?



"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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+(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


Please Help! Thank you,

Aviator

On Wed, 12 Jan 2005 04:25:03 -0800, "Aviator"
wrote:

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,Holida ys))


--ron

On Wed, 12 Jan 2005 20:15:37 -0500, Ron Rosenfeld
wrote:

On Wed, 12 Jan 2005 04:25:03 -0800, "Aviator"
wrote:

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,Holid ays))


--ron

TYPO Alert: with the IF, should read:

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


--ron