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

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

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

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

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