Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtracting Holidays from Calendar Days
Hi All,
I know how to subtract holidays from NETWORKDAYS in a formula by defining them (as in Christmas Day 25/12/2008), but I have a process that is giving me a headache. It is measured in calendar days, so at present I am doing a simple subtraction of start date cell - end date cell of process. However, I obviously cannot account for any holidays in year. Is there a formula/way of doing this? Thanks, Drew |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtracting Holidays from Calendar Days
Assuming start date = A2, end date = A3, holidays in B2:B50
=A3-A2-COUNTIF(B2:B50,"="&A2)+COUNTIF(B2:B50,""&A3) Adjust cell references as needed. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Drewpotlad" wrote: Hi All, I know how to subtract holidays from NETWORKDAYS in a formula by defining them (as in Christmas Day 25/12/2008), but I have a process that is giving me a headache. It is measured in calendar days, so at present I am doing a simple subtraction of start date cell - end date cell of process. However, I obviously cannot account for any holidays in year. Is there a formula/way of doing this? Thanks, Drew |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtracting Holidays from Calendar Days
On Wed, 18 Feb 2009 06:05:01 -0800, Drewpotlad
wrote: Hi All, I know how to subtract holidays from NETWORKDAYS in a formula by defining them (as in Christmas Day 25/12/2008), but I have a process that is giving me a headache. It is measured in calendar days, so at present I am doing a simple subtraction of start date cell - end date cell of process. However, I obviously cannot account for any holidays in year. Is there a formula/way of doing this? Thanks, Drew Not sure exactly what you want. How does a "calendar day" differ from a "day"? Do you want to include or exclude weekend days? What do you mean by "account for any holidays in year"? Do you want to include or exclude them? --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtracting Holidays from Calendar Days
"Ron Rosenfeld" wrote: On Wed, 18 Feb 2009 06:05:01 -0800, Drewpotlad wrote: Hi All, I know how to subtract holidays from NETWORKDAYS in a formula by defining them (as in Christmas Day 25/12/2008), but I have a process that is giving me a headache. It is measured in calendar days, so at present I am doing a simple subtraction of start date cell - end date cell of process. However, I obviously cannot account for any holidays in year. Is there a formula/way of doing this? Thanks, Drew Not sure exactly what you want. How does a "calendar day" differ from a "day"? Do you want to include or exclude weekend days? What do you mean by "account for any holidays in year"? Do you want to include or exclude them? --ron Hi Ron, Basically I want to count all days including weekends. What I want to exclude are all public holidays, such as Christmas/New Years Day and Easter Holidays (as well as all other British Public Holidays) Thanks, Drew |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtracting Holidays from Calendar Days
On Thu, 19 Feb 2009 02:04:00 -0800, Drewpotlad
wrote: "Ron Rosenfeld" wrote: On Wed, 18 Feb 2009 06:05:01 -0800, Drewpotlad wrote: Hi All, I know how to subtract holidays from NETWORKDAYS in a formula by defining them (as in Christmas Day 25/12/2008), but I have a process that is giving me a headache. It is measured in calendar days, so at present I am doing a simple subtraction of start date cell - end date cell of process. However, I obviously cannot account for any holidays in year. Is there a formula/way of doing this? Thanks, Drew Not sure exactly what you want. How does a "calendar day" differ from a "day"? Do you want to include or exclude weekend days? What do you mean by "account for any holidays in year"? Do you want to include or exclude them? --ron Hi Ron, Basically I want to count all days including weekends. What I want to exclude are all public holidays, such as Christmas/New Years Day and Easter Holidays (as well as all other British Public Holidays) Thanks, Drew Since you are comparing this to the NETWORKDAYS function, I will make the assumption that you want an *inclusive* count (i.e. counting both the start and end dates), which is how the NETWORKDAYS function operates. That being the case: Set up a range someplace and NAME it "Holidays" (or use the absolute cell references, if you prefer). Then try this formula: =SUMPRODUCT(--(ISNA(MATCH(ROW(INDIRECT(Start_Date&":"&End_Date)) ,Holidays,0)))) Any date from Start to End will return #NA *unless* it is also found in your list of holidays. So we just count up the number of NA's. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Employee days worked (-Holidays, -weekends, Snow Days, etc) | Excel Discussion (Misc queries) | |||
Add days to a date, but exclude holidays | Excel Worksheet Functions | |||
calculating days + holidays | Excel Worksheet Functions | |||
Generating business days in a calendar month, EXCLUDING holidays | Excel Worksheet Functions | |||
Generating business days in a calendar month, EXCLUDING holidays | Excel Worksheet Functions |