Date Calculation question
 

I was able to successfully use someone's help on this site to calculate the
following

Add 60 Calendar dates, excluding holidays with the holidays defined in a
hidden column. using the following formula:
A2-A1-COUNTIF(C1:C7,""&A)+COUNTIF(C1:C7,""&A2)...

While this excludes returning a Holiday date, it doesn't add days in lieu of
the Holiday for the returned date..example If I want to count 45 Calendar
days from 11/20/09, I do not want it to land on Thanksgiving or Christmas, I
want it two additional days if that occurs? How can I incomporate this in
the above formula?

Thanks,
Amanda

Date Calculation question
 

Why don't you just use Workday

=WORKDAY(A1,A2,C1:C7)

--
__________________________________
HTH

Bob

"Amanda" wrote in message
...
I was able to successfully use someone's help on this site to calculate the
following

Add 60 Calendar dates, excluding holidays with the holidays defined in a
hidden column. using the following formula:
A2-A1-COUNTIF(C1:C7,""&A)+COUNTIF(C1:C7,""&A2)...

While this excludes returning a Holiday date, it doesn't add days in lieu
of
the Holiday for the returned date..example If I want to count 45 Calendar
days from 11/20/09, I do not want it to land on Thanksgiving or Christmas,
I
want it two additional days if that occurs? How can I incomporate this in
the above formula?

Thanks,
Amanda

Date Calculation question
 

OP is not excluding weekends, only holidays.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Bob Phillips" wrote:

Why don't you just use Workday

=WORKDAY(A1,A2,C1:C7)

--
__________________________________
HTH

Bob

"Amanda" wrote in message
...
I was able to successfully use someone's help on this site to calculate the
following

Add 60 Calendar dates, excluding holidays with the holidays defined in a
hidden column. using the following formula:
A2-A1-COUNTIF(C1:C7,""&A)+COUNTIF(C1:C7,""&A2)...

While this excludes returning a Holiday date, it doesn't add days in lieu
of
the Holiday for the returned date..example If I want to count 45 Calendar
days from 11/20/09, I do not want it to land on Thanksgiving or Christmas,
I
want it two additional days if that occurs? How can I incomporate this in
the above formula?

Thanks,
Amanda

Date Calculation question
 

Might be easier to do with a UDF (user-defined-function). Open up the VBE by
pressing Alt+F11. Then goto Insert - Module. Paste this in:

'========
Function ExcludeDays(Start_Date As Date, Days As Integer, _
Holidays As Range) As Date

If Days < 0 Then
ExcludeDays = "#VALUE!"
Exit Function
End If

Do While Days 0
Start_Date = Start_Date + 1
For Each cell In Holidays
If Start_Date = cell.Value Then
'If a holiday, don't count
x = 0
Exit For
Else
'If not a holiday, count it
x = 1
End If
Next

Days = Days - x
Loop

ExcludeDays = Start_Date

End Function
'==============

Back in your worbook, your formula becomes:
=ExcludeDays(A1,60,C1:C7)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Amanda" wrote:

I was able to successfully use someone's help on this site to calculate the
following

Add 60 Calendar dates, excluding holidays with the holidays defined in a
hidden column. using the following formula:
A2-A1-COUNTIF(C1:C7,""&A)+COUNTIF(C1:C7,""&A2)...

While this excludes returning a Holiday date, it doesn't add days in lieu of
the Holiday for the returned date..example If I want to count 45 Calendar
days from 11/20/09, I do not want it to land on Thanksgiving or Christmas, I
want it two additional days if that occurs? How can I incomporate this in
the above formula?

Thanks,
Amanda