Add days to a date, but exclude holidays
 

I have a set date to which I need to add a number of days, but holidays
should not be counted. The cells of one of the columns in my spreadsheet
holds the number of days, and I'd like to have results returned in the cell
in the neighboring column.

Something like this:

8/2/2008

5 8/7/2008
10 8/12/2008
30 9/2/2008 (30 days is 9/1, but that's a holiday, so 9/2 is
right)

Any ideas?

I had been doing A1+B1 and it worked fine until I ran into needing to add
Business Days instead of "regular days". So, I stared using =WORKDAY(A1,5).
Neither excludes holidays though. Is there any way to have the number of
days argument be a reference to another cell (b1 in my example) rather than
having to put it in by hand?

Thanks!
J

Add days to a date, but exclude holidays
 

WORKDAY excludes holidays, it is the 3rd argument

=WORKDAY(A1,5,holiday)

where holidays is a rang of holiday dates.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Chickadee" wrote in message
...
I have a set date to which I need to add a number of days, but holidays
should not be counted. The cells of one of the columns in my spreadsheet
holds the number of days, and I'd like to have results returned in the
cell
in the neighboring column.

Something like this:

8/2/2008

5 8/7/2008
10 8/12/2008
30 9/2/2008 (30 days is 9/1, but that's a holiday, so 9/2 is
right)

Any ideas?

I had been doing A1+B1 and it worked fine until I ran into needing to add
Business Days instead of "regular days". So, I stared using
=WORKDAY(A1,5).
Neither excludes holidays though. Is there any way to have the number of
days argument be a reference to another cell (b1 in my example) rather
than
having to put it in by hand?

Thanks!
J

Add days to a date, but exclude holidays
 

Actually, workday function does include a 3rd parameter, holidays.
WORKDAY(start_date,days,holidays)

where holidays is a range that has the dates of holidays.
--
John C


"Chickadee" wrote:

I have a set date to which I need to add a number of days, but holidays
should not be counted. The cells of one of the columns in my spreadsheet
holds the number of days, and I'd like to have results returned in the cell
in the neighboring column.

Something like this:

8/2/2008

5 8/7/2008
10 8/12/2008
30 9/2/2008 (30 days is 9/1, but that's a holiday, so 9/2 is
right)

Any ideas?

I had been doing A1+B1 and it worked fine until I ran into needing to add
Business Days instead of "regular days". So, I stared using =WORKDAY(A1,5).
Neither excludes holidays though. Is there any way to have the number of
days argument be a reference to another cell (b1 in my example) rather than
having to put it in by hand?

Thanks!
J

Add days to a date, but exclude holidays
 

Have a look at NETWORKDAYS (part of the Analysis Tool-pack add-in) -
you can specify a holiday list with it.

Hope this helps.

Pete

On Jul 30, 3:39*pm, Chickadee
wrote:
I have a set date to which I need to add a number of days, but holidays
should not be counted. *The cells of one of the columns in my spreadsheet
holds the number of days, and I'd like to have results returned in the cell
in the neighboring column.

Something like this:

8/2/2008

5 * * * * * * * 8/7/2008
10 * * * * * * 8/12/2008
30 * * * * * * 9/2/2008 (30 days is 9/1, but that's a holiday, so 9/2 is
right)

Any ideas? *

I had been doing A1+B1 and it worked fine until I ran into needing to add
Business Days instead of "regular days". *So, I stared using =WORKDAY(A1,5). *
Neither excludes holidays though. *Is there any way to have the number of
days argument be a reference to another cell (b1 in my example) rather than
having to put it in by hand?

Thanks!
J

Add days to a date, but exclude holidays
 

She knows how many days, not when <g

--
__________________________________
HTH

Bob

"Pete_UK" wrote in message
...
Have a look at NETWORKDAYS (part of the Analysis Tool-pack add-in) -
you can specify a holiday list with it.

Hope this helps.

Pete

On Jul 30, 3:39 pm, Chickadee
wrote:
I have a set date to which I need to add a number of days, but holidays
should not be counted. The cells of one of the columns in my spreadsheet
holds the number of days, and I'd like to have results returned in the
cell
in the neighboring column.

Something like this:

8/2/2008

5 8/7/2008
10 8/12/2008
30 9/2/2008 (30 days is 9/1, but that's a holiday, so 9/2 is
right)

Any ideas?

I had been doing A1+B1 and it worked fine until I ran into needing to add
Business Days instead of "regular days". So, I stared using
=WORKDAY(A1,5).
Neither excludes holidays though. Is there any way to have the number of
days argument be a reference to another cell (b1 in my example) rather
than
having to put it in by hand?

Thanks!
J

Add days to a date, but exclude holidays
 

Sorry, guys, I guess I wasn't clear. It's not the holidays part that's
stumping me, I get that.

What I need is a function that works like WORKDAY (in that there are
arguments you can use - including "holiday"), but that is just for plain old
days (not just workdays). I also need to know how, if possible, to point the
second argument (the number of days to add to the fixed date) to a number in
another cell rather than typing it in the formula by hand. In the
alternative, can you add a holiday argument to a simple formula like A1+B1?

By way of explanation: I use this spreadsheet as a template for real estate
closing checklist/deadlines so the number of days from the fixed date for a
given event to occur is not the same for different deals. Sometimes earnest
money is due 5 days from the date of signing, sometimes 10 days. I would
rather change the "5" to a "10" in it's own cell than have to change the
argument in the date calculating function in the neighboring cell.

"Bob Phillips" wrote:

WORKDAY excludes holidays, it is the 3rd argument

=WORKDAY(A1,5,holiday)

where holidays is a rang of holiday dates.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Chickadee" wrote in message
...
I have a set date to which I need to add a number of days, but holidays
should not be counted. The cells of one of the columns in my spreadsheet
holds the number of days, and I'd like to have results returned in the
cell
in the neighboring column.

Something like this:

8/2/2008

5 8/7/2008
10 8/12/2008
30 9/2/2008 (30 days is 9/1, but that's a holiday, so 9/2 is
right)

Any ideas?

I had been doing A1+B1 and it worked fine until I ran into needing to add
Business Days instead of "regular days". So, I stared using
=WORKDAY(A1,5).
Neither excludes holidays though. Is there any way to have the number of
days argument be a reference to another cell (b1 in my example) rather
than
having to put it in by hand?

Thanks!
J

Add days to a date, but exclude holidays
 

Bob Phillips actually created a UDF that would be able to handle your
situation.

Do a search with the words: include saturday for workday. there were 2
messages that came up, read up on the first one.
--
John C


"Chickadee" wrote:

Sorry, guys, I guess I wasn't clear. It's not the holidays part that's
stumping me, I get that.

What I need is a function that works like WORKDAY (in that there are
arguments you can use - including "holiday"), but that is just for plain old
days (not just workdays). I also need to know how, if possible, to point the
second argument (the number of days to add to the fixed date) to a number in
another cell rather than typing it in the formula by hand. In the
alternative, can you add a holiday argument to a simple formula like A1+B1?

By way of explanation: I use this spreadsheet as a template for real estate
closing checklist/deadlines so the number of days from the fixed date for a
given event to occur is not the same for different deals. Sometimes earnest
money is due 5 days from the date of signing, sometimes 10 days. I would
rather change the "5" to a "10" in it's own cell than have to change the
argument in the date calculating function in the neighboring cell.

"Bob Phillips" wrote:

WORKDAY excludes holidays, it is the 3rd argument

=WORKDAY(A1,5,holiday)

where holidays is a rang of holiday dates.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Chickadee" wrote in message
...
I have a set date to which I need to add a number of days, but holidays
should not be counted. The cells of one of the columns in my spreadsheet
holds the number of days, and I'd like to have results returned in the
cell
in the neighboring column.

Something like this:

8/2/2008

5 8/7/2008
10 8/12/2008
30 9/2/2008 (30 days is 9/1, but that's a holiday, so 9/2 is
right)

Any ideas?

I had been doing A1+B1 and it worked fine until I ran into needing to add
Business Days instead of "regular days". So, I stared using
=WORKDAY(A1,5).
Neither excludes holidays though. Is there any way to have the number of
days argument be a reference to another cell (b1 in my example) rather
than
having to put it in by hand?

Thanks!
J

Add days to a date, but exclude holidays
 

On Wed, 30 Jul 2008 08:56:01 -0700, Chickadee
wrote:

By way of explanation: I use this spreadsheet as a template for real estate
closing checklist/deadlines so the number of days from the fixed date for a
given event to occur is not the same for different deals. Sometimes earnest
money is due 5 days from the date of signing, sometimes 10 days. I would
rather change the "5" to a "10" in it's own cell than have to change the
argument in the date calculating function in the neighboring cell.

Would earnest money be due on a non-business day? In other words, would
earnest money be due on a Saturday or Sunday?

Or is it the first business day after the 5 days or 10 days?

Assuming the earnest money is due on the first business day after 5 or 10
calendar days, you could do the following.

1. Put your list of holiday dates in a NAME'd range name'd Holidays.

A1: Fixed Date
A2: Number of days to count
A3: =WORKDAY(A1+A2-1,1,Holidays)

If you mean something else, please be more specific.
--ron

Add days to a date, but exclude holidays
 

Ron, in my case I'm not worried about deadlines falling on weekends.
John, I looked at the previous post you pointed me to and I hate to be
obtuse, but it's really all over my head. I get how/where to put my data
into this formula and understand (I think) that it is essentially the WORKDAY
function and format but it includes Satudays in it's count of "days" from
"start_date", but could you help me modify it to include Sundays in the count
as well?

=start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKD AY(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(da ys)*10))),2)<7)*ISNA(MATCH(start_date+SIGN(days)*( ROW(INDIRECT("1:"&ABS(days)*10))),holidays,0)),ROW (INDIRECT("1:"&ABS(days)*10))),ABS(days)))

"Ron Rosenfeld" wrote:

On Wed, 30 Jul 2008 08:56:01 -0700, Chickadee
wrote:

By way of explanation: I use this spreadsheet as a template for real estate
closing checklist/deadlines so the number of days from the fixed date for a
given event to occur is not the same for different deals. Sometimes earnest
money is due 5 days from the date of signing, sometimes 10 days. I would
rather change the "5" to a "10" in it's own cell than have to change the
argument in the date calculating function in the neighboring cell.

Would earnest money be due on a non-business day? In other words, would
earnest money be due on a Saturday or Sunday?

Or is it the first business day after the 5 days or 10 days?

Assuming the earnest money is due on the first business day after 5 or 10
calendar days, you could do the following.

1. Put your list of holiday dates in a NAME'd range name'd Holidays.

A1: Fixed Date
A2: Number of days to count
A3: =WORKDAY(A1+A2-1,1,Holidays)

If you mean something else, please be more specific.
--ron

Add days to a date, but exclude holidays
 

=start_date+IF(days=0,0,SIGN(days)*SMALL(IF(ISNA(M ATCH(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS( days)*10))),holidays,0)),ROW(INDIRECT("1:"&ABS(day s)*10))),ABS(days)))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Chickadee" wrote in message
...
Ron, in my case I'm not worried about deadlines falling on weekends.
John, I looked at the previous post you pointed me to and I hate to be
obtuse, but it's really all over my head. I get how/where to put my data
into this formula and understand (I think) that it is essentially the
WORKDAY
function and format but it includes Satudays in it's count of "days" from
"start_date", but could you help me modify it to include Sundays in the
count
as well?

=start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKD AY(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(da ys)*10))),2)<7)*ISNA(MATCH(start_date+SIGN(days)*( ROW(INDIRECT("1:"&ABS(days)*10))),holidays,0)),ROW (INDIRECT("1:"&ABS(days)*10))),ABS(days)))

"Ron Rosenfeld" wrote:

On Wed, 30 Jul 2008 08:56:01 -0700, Chickadee
wrote:

By way of explanation: I use this spreadsheet as a template for real
estate
closing checklist/deadlines so the number of days from the fixed date
for a
given event to occur is not the same for different deals. Sometimes
earnest
money is due 5 days from the date of signing, sometimes 10 days. I
would
rather change the "5" to a "10" in it's own cell than have to change the
argument in the date calculating function in the neighboring cell.

Would earnest money be due on a non-business day? In other words, would
earnest money be due on a Saturday or Sunday?

Or is it the first business day after the 5 days or 10 days?

Assuming the earnest money is due on the first business day after 5 or 10
calendar days, you could do the following.

1. Put your list of holiday dates in a NAME'd range name'd Holidays.

A1: Fixed Date
A2: Number of days to count
A3: =WORKDAY(A1+A2-1,1,Holidays)

If you mean something else, please be more specific.
--ron

Add days to a date, but exclude holidays
 

On Wed, 30 Jul 2008 13:18:12 -0700, Chickadee
wrote:

Ron, in my case I'm not worried about deadlines falling on weekends.

In that case, all you need to do is add the number of days to the start date,
and, since you don't want to count holidays, add to this the number of holiday
days that fall within the range.

This **array-entered** formula will do that.

To **array-enter** a formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula if you did it correctly.

The formula assumes the following:

A1: Start Date
A2: Number of days until earnest money is due.
Holidays: A NAME'd range in a column (vertical) wherein you list the holiday
dates.

=A1+A2+SUM(--(ROW(INDIRECT(A1&":"&A1+A2))=TRANSPOSE(Holidays)))

--ron

Add days to a date, but exclude holidays
 

Surely, it is not that simple. That formula is not recursive, in that days
will calculate the date beyond, any holiday dates within that range will
increment the date by that number, but if the added on days also includes a
date within the holiday range it doesn't increment again.

For example, a start date of 14th July and 10 days addon, with holiday dates
of 16th July and 25th July returns the 25th, not the 26th.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Ron Rosenfeld" wrote in message
...
On Wed, 30 Jul 2008 13:18:12 -0700, Chickadee
wrote:

Ron, in my case I'm not worried about deadlines falling on weekends.

In that case, all you need to do is add the number of days to the start
date,
and, since you don't want to count holidays, add to this the number of
holiday
days that fall within the range.

This **array-entered** formula will do that.

To **array-enter** a formula, hold down <ctrl<shift while hitting
<enter.
Excel will place braces {...} around the formula if you did it correctly.

The formula assumes the following:

A1: Start Date
A2: Number of days until earnest money is due.
Holidays: A NAME'd range in a column (vertical) wherein you list the
holiday
dates.

=A1+A2+SUM(--(ROW(INDIRECT(A1&":"&A1+A2))=TRANSPOSE(Holidays)))

--ron

Add days to a date, but exclude holidays
 

On Thu, 31 Jul 2008 08:43:45 +0100, "Bob Phillips"
wrote:

Surely, it is not that simple. That formula is not recursive, in that days
will calculate the date beyond, any holiday dates within that range will
increment the date by that number, but if the added on days also includes a
date within the holiday range it doesn't increment again.

For example, a start date of 14th July and 10 days addon, with holiday dates
of 16th July and 25th July returns the 25th, not the 26th.

Excellent Point. Thanks for picking that up.
--ron

Add days to a date, but exclude holidays
 

Thank you, Bob, this works perfectly! Some day I will endeavor to try and
figure out how it works.


"Bob Phillips" wrote:

=start_date+IF(days=0,0,SIGN(days)*SMALL(IF(ISNA(M ATCH(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS( days)*10))),holidays,0)),ROW(INDIRECT("1:"&ABS(day s)*10))),ABS(days)))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Chickadee" wrote in message
...
Ron, in my case I'm not worried about deadlines falling on weekends.
John, I looked at the previous post you pointed me to and I hate to be
obtuse, but it's really all over my head. I get how/where to put my data
into this formula and understand (I think) that it is essentially the
WORKDAY
function and format but it includes Satudays in it's count of "days" from
"start_date", but could you help me modify it to include Sundays in the
count
as well?

=start_date+IF(days=0,0,SIGN(days)*SMALL(IF((WEEKD AY(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(da ys)*10))),2)<7)*ISNA(MATCH(start_date+SIGN(days)*( ROW(INDIRECT("1:"&ABS(days)*10))),holidays,0)),ROW (INDIRECT("1:"&ABS(days)*10))),ABS(days)))

"Ron Rosenfeld" wrote:

On Wed, 30 Jul 2008 08:56:01 -0700, Chickadee
wrote:

By way of explanation: I use this spreadsheet as a template for real
estate
closing checklist/deadlines so the number of days from the fixed date
for a
given event to occur is not the same for different deals. Sometimes
earnest
money is due 5 days from the date of signing, sometimes 10 days. I
would
rather change the "5" to a "10" in it's own cell than have to change the
argument in the date calculating function in the neighboring cell.

Would earnest money be due on a non-business day? In other words, would
earnest money be due on a Saturday or Sunday?

Or is it the first business day after the 5 days or 10 days?

Assuming the earnest money is due on the first business day after 5 or 10
calendar days, you could do the following.

1. Put your list of holiday dates in a NAME'd range name'd Holidays.

A1: Fixed Date
A2: Number of days to count
A3: =WORKDAY(A1+A2-1,1,Holidays)

If you mean something else, please be more specific.
--ron