Add days to a date, but exclude holidays

[Chickadee]

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

 

[Bob Phillips]

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)

 

[John C]

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.

 

[Pete_UK]

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

 

[Bob Phillips]

She knows how many days, not when <g>

--
__________________________________
HTH

Bob

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

 

[Chickadee]

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.

 

[John C]

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.

 

[Ron Rosenfeld]

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

 

[Chickadee]

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((WEEKDAY(start_date+SIGN(days)*(ROW(INDIRECT("1:"&ABS(days)*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)))

 

[Bob Phillips]

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

--
HTH

Bob

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

 

[Ron Rosenfeld]

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

 

[Bob Phillips]

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]

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

 

[Chickadee]

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