Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date calculation question | New Users to Excel | |||
Date calculation across workbooks / Workbook naming question. | Excel Discussion (Misc queries) | |||
Tricky Date calculation: How to calculate a future date | Excel Discussion (Misc queries) | |||
A simple date calculation question. | Excel Discussion (Misc queries) | |||
A simple date calculation question. | Excel Discussion (Misc queries) |