Ok, I am trying to create a function that does the following..
Adds X amount of days to a particular day including ALL days but returning a
result that is not a weekend or holiday.
I have a grid of dates with a vertical range of saturday dates, sunday dates
and an array of holiday dates.
Essentially, I have been trying to add, say, 60 days to a date. Then trying
to compare it with vlookup to each one of these using the vlookup's below b4
is the date, c2= 60, if its true it lists the date that matches.
=VLOOKUP(B4+C2,l4:l56,1,FALSE)
=VLOOKUP(B4+C2,A4:A56,1,FALSE)
=VLOOKUP(B4+C2,A59:e65,1,FALSE)
What I've tried to do is add 1 or 2 days to make it reach the next
non-holiday weekday date. But my syntax on the functions is not working out.
Any help is appreciated

Take a look WORKDAY function


"Dolt" wrote:

Ok, I am trying to create a function that does the following..
Adds X amount of days to a particular day including ALL days but returning a
result that is not a weekend or holiday.
I have a grid of dates with a vertical range of saturday dates, sunday dates
and an array of holiday dates.
Essentially, I have been trying to add, say, 60 days to a date. Then trying
to compare it with vlookup to each one of these using the vlookup's below b4
is the date, c2= 60, if its true it lists the date that matches.
=VLOOKUP(B4+C2,l4:l56,1,FALSE)
=VLOOKUP(B4+C2,A4:A56,1,FALSE)
=VLOOKUP(B4+C2,A59:e65,1,FALSE)
What I've tried to do is add 1 or 2 days to make it reach the next
non-holiday weekday date. But my syntax on the functions is not working out.
Any help is appreciated

I have already set up the spreadsheet to find the next date excluding
counting weekends and holidays but I want to count ALL those days (holidays &
weekends) to find the next date. So, if the say 60th day ends on a holiday or
weekend the result I get back is the next available workday.

"Teethless mama" wrote:

Take a look WORKDAY function


"Dolt" wrote:

Ok, I am trying to create a function that does the following..
Adds X amount of days to a particular day including ALL days but returning a
result that is not a weekend or holiday.
I have a grid of dates with a vertical range of saturday dates, sunday dates
and an array of holiday dates.
Essentially, I have been trying to add, say, 60 days to a date. Then trying
to compare it with vlookup to each one of these using the vlookup's below b4
is the date, c2= 60, if its true it lists the date that matches.
=VLOOKUP(B4+C2,l4:l56,1,FALSE)
=VLOOKUP(B4+C2,A4:A56,1,FALSE)
=VLOOKUP(B4+C2,A59:e65,1,FALSE)
What I've tried to do is add 1 or 2 days to make it reach the next
non-holiday weekday date. But my syntax on the functions is not working out.
Any help is appreciated

Let's say
A1: Start date
A2: number of workdays
A3:A5 Holidays

=WORKDAY(A1,A2,A3:A5)

Adjust to suit your needed


"Dolt" wrote:

I have already set up the spreadsheet to find the next date excluding
counting weekends and holidays but I want to count ALL those days (holidays &
weekends) to find the next date. So, if the say 60th day ends on a holiday or
weekend the result I get back is the next available workday.

"Teethless mama" wrote:

Take a look WORKDAY function


"Dolt" wrote:

Ok, I am trying to create a function that does the following..
Adds X amount of days to a particular day including ALL days but returning a
result that is not a weekend or holiday.
I have a grid of dates with a vertical range of saturday dates, sunday dates
and an array of holiday dates.
Essentially, I have been trying to add, say, 60 days to a date. Then trying
to compare it with vlookup to each one of these using the vlookup's below b4
is the date, c2= 60, if its true it lists the date that matches.
=VLOOKUP(B4+C2,l4:l56,1,FALSE)
=VLOOKUP(B4+C2,A4:A56,1,FALSE)
=VLOOKUP(B4+C2,A59:e65,1,FALSE)
What I've tried to do is add 1 or 2 days to make it reach the next
non-holiday weekday date. But my syntax on the functions is not working out.
Any help is appreciated

The issue with your response is that the function you suggest will only count
workdays. As I indicated in my reply I want to count weekend days and
holidays.
Thanks

"Teethless mama" wrote:

Let's say
A1: Start date
A2: number of workdays
A3:A5 Holidays

=WORKDAY(A1,A2,A3:A5)

Adjust to suit your needed


"Dolt" wrote:

I have already set up the spreadsheet to find the next date excluding
counting weekends and holidays but I want to count ALL those days (holidays &
weekends) to find the next date. So, if the say 60th day ends on a holiday or
weekend the result I get back is the next available workday.

"Teethless mama" wrote:

Take a look WORKDAY function


"Dolt" wrote:

Ok, I am trying to create a function that does the following..
Adds X amount of days to a particular day including ALL days but returning a
result that is not a weekend or holiday.
I have a grid of dates with a vertical range of saturday dates, sunday dates
and an array of holiday dates.
Essentially, I have been trying to add, say, 60 days to a date. Then trying
to compare it with vlookup to each one of these using the vlookup's below b4
is the date, c2= 60, if its true it lists the date that matches.
=VLOOKUP(B4+C2,l4:l56,1,FALSE)
=VLOOKUP(B4+C2,A4:A56,1,FALSE)
=VLOOKUP(B4+C2,A59:e65,1,FALSE)
What I've tried to do is add 1 or 2 days to make it reach the next
non-holiday weekday date. But my syntax on the functions is not working out.
Any help is appreciated

On Thu, 8 Feb 2007 11:57:01 -0800, Dolt wrote:

Ok, I am trying to create a function that does the following..
Adds X amount of days to a particular day including ALL days but returning a
result that is not a weekend or holiday.
I have a grid of dates with a vertical range of saturday dates, sunday dates
and an array of holiday dates.
Essentially, I have been trying to add, say, 60 days to a date. Then trying
to compare it with vlookup to each one of these using the vlookup's below b4
is the date, c2= 60, if its true it lists the date that matches.
=VLOOKUP(B4+C2,l4:l56,1,FALSE)
=VLOOKUP(B4+C2,A4:A56,1,FALSE)
=VLOOKUP(B4+C2,A59:e65,1,FALSE)
What I've tried to do is add 1 or 2 days to make it reach the next
non-holiday weekday date. But my syntax on the functions is not working out.
Any help is appreciated


=WORKDAY(B4+C2-1,1,holidays)

should do what you require.

If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.

--ron

I don't know understand why it works but it does
Thanks

"Ron Rosenfeld" wrote:

On Thu, 8 Feb 2007 11:57:01 -0800, Dolt wrote:

Ok, I am trying to create a function that does the following..
Adds X amount of days to a particular day including ALL days but returning a
result that is not a weekend or holiday.
I have a grid of dates with a vertical range of saturday dates, sunday dates
and an array of holiday dates.
Essentially, I have been trying to add, say, 60 days to a date. Then trying
to compare it with vlookup to each one of these using the vlookup's below b4
is the date, c2= 60, if its true it lists the date that matches.
=VLOOKUP(B4+C2,l4:l56,1,FALSE)
=VLOOKUP(B4+C2,A4:A56,1,FALSE)
=VLOOKUP(B4+C2,A59:e65,1,FALSE)
What I've tried to do is add 1 or 2 days to make it reach the next
non-holiday weekday date. But my syntax on the functions is not working out.
Any help is appreciated


=WORKDAY(B4+C2-1,1,holidays)

should do what you require.

If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.

--ron

On Thu, 8 Feb 2007 13:44:00 -0800, Dolt wrote:

I don't know understand why it works but it does
Thanks


You're welcome.

How it works:

1. Take original date
2. Add one less than the required number of days
3. Then add one Workday.

If adding one workday results in a weekend day or holiday, the Workday function
will skip over that.


--ron

Ron:

Thank you for sharing your knowledge.

I'm running Excel 2003 on Windows XP. I've followed the steps you described
in this earlier thread, using the =WORKDAY formula. Cell A4 contains my
original date and has a date format. Cell B3 has the number 10 (for the
number of workdays) and is general format (I've also tried number format).
The formula of =WORKDAY(A4+B3-1,1,holidays) is in cell B4 and that had a date
format as well. I am getting the #NAME? error. I have the Analysis ToolPak
add-in showing in Tools.

Can you think of something I may be doing wrong?

Thanks, in advance, for your consideration.

GAF

"Ron Rosenfeld" wrote:

On Thu, 8 Feb 2007 13:44:00 -0800, Dolt wrote:

I don't know understand why it works but it does
Thanks


You're welcome.

How it works:

1. Take original date
2. Add one less than the required number of days
3. Then add one Workday.

If adding one workday results in a weekend day or holiday, the Workday function
will skip over that.


--ron

Do you have a range named holidays where you put public holidays?


--


Regards,


Peo Sjoblom

"gafern" wrote in message
...
Ron:

Thank you for sharing your knowledge.

I'm running Excel 2003 on Windows XP. I've followed the steps you
described
in this earlier thread, using the =WORKDAY formula. Cell A4 contains my
original date and has a date format. Cell B3 has the number 10 (for the
number of workdays) and is general format (I've also tried number format).
The formula of =WORKDAY(A4+B3-1,1,holidays) is in cell B4 and that had a
date
format as well. I am getting the #NAME? error. I have the Analysis
ToolPak
add-in showing in Tools.

Can you think of something I may be doing wrong?

Thanks, in advance, for your consideration.

GAF

"Ron Rosenfeld" wrote:

On Thu, 8 Feb 2007 13:44:00 -0800, Dolt
wrote:

I don't know understand why it works but it does
Thanks


You're welcome.

How it works:

1. Take original date
2. Add one less than the required number of days
3. Then add one Workday.

If adding one workday results in a weekend day or holiday, the Workday
function
will skip over that.


--ron

No, I don't. I was thinking this formula would be used to identify the next
workday after a weekend. For example, if something was received 07/30/08,
and is due 10 days from that date, it would be due 08/09/08. However, since
that date falls on a weekend, I'd want the due date in the spreadsheet to
show up as 08/11/08, the first available work day. I guess I'm using the
wrong formula, or maybe I'm doing something else wrong.

Thanks for your help.

GAF

"Peo Sjoblom" wrote:

Do you have a range named holidays where you put public holidays?


--


Regards,


Peo Sjoblom

"gafern" wrote in message
...
Ron:

Thank you for sharing your knowledge.

I'm running Excel 2003 on Windows XP. I've followed the steps you
described
in this earlier thread, using the =WORKDAY formula. Cell A4 contains my
original date and has a date format. Cell B3 has the number 10 (for the
number of workdays) and is general format (I've also tried number format).
The formula of =WORKDAY(A4+B3-1,1,holidays) is in cell B4 and that had a
date
format as well. I am getting the #NAME? error. I have the Analysis
ToolPak
add-in showing in Tools.

Can you think of something I may be doing wrong?

Thanks, in advance, for your consideration.

GAF

"Ron Rosenfeld" wrote:

On Thu, 8 Feb 2007 13:44:00 -0800, Dolt
wrote:

I don't know understand why it works but it does
Thanks


You're welcome.

How it works:

1. Take original date
2. Add one less than the required number of days
3. Then add one Workday.

If adding one workday results in a weekend day or holiday, the Workday
function
will skip over that.


--ron

Well you can't use a name like holidays if you don't have defined as a name.
If you don't want any public holidays in your calculations just leave that
part out

=WORKDAY(A4+B3-1,1)


if you want to exclude holidays put the public holidays for 2008 in a range
like H1:H10,
select H1:H10 and in the name box above the header of column A type Holidays
and press enter
Now your first formula should work. Of course you can leave the range empty
and add the dates later
but once you have named the range you shouldn't get a name error anymore as
long as ATP is
installed

--


Regards,


Peo Sjoblom

"gafern" wrote in message
...
No, I don't. I was thinking this formula would be used to identify the
next
workday after a weekend. For example, if something was received 07/30/08,
and is due 10 days from that date, it would be due 08/09/08. However,
since
that date falls on a weekend, I'd want the due date in the spreadsheet to
show up as 08/11/08, the first available work day. I guess I'm using the
wrong formula, or maybe I'm doing something else wrong.

Thanks for your help.

GAF

"Peo Sjoblom" wrote:

Do you have a range named holidays where you put public holidays?


--


Regards,


Peo Sjoblom

"gafern" wrote in message
...
Ron:

Thank you for sharing your knowledge.

I'm running Excel 2003 on Windows XP. I've followed the steps you
described
in this earlier thread, using the =WORKDAY formula. Cell A4 contains
my
original date and has a date format. Cell B3 has the number 10 (for the
number of workdays) and is general format (I've also tried number
format).
The formula of =WORKDAY(A4+B3-1,1,holidays) is in cell B4 and that had
a
date
format as well. I am getting the #NAME? error. I have the Analysis
ToolPak
add-in showing in Tools.

Can you think of something I may be doing wrong?

Thanks, in advance, for your consideration.

GAF

"Ron Rosenfeld" wrote:

On Thu, 8 Feb 2007 13:44:00 -0800, Dolt

wrote:

I don't know understand why it works but it does
Thanks


You're welcome.

How it works:

1. Take original date
2. Add one less than the required number of days
3. Then add one Workday.

If adding one workday results in a weekend day or holiday, the Workday
function
will skip over that.


--ron

Thank you. I'd obviously overlooked defining a name for public holidays.
I'll give this try.

Thanks, again!

GAF

"Peo Sjoblom" wrote:

Well you can't use a name like holidays if you don't have defined as a name.
If you don't want any public holidays in your calculations just leave that
part out

=WORKDAY(A4+B3-1,1)


if you want to exclude holidays put the public holidays for 2008 in a range
like H1:H10,
select H1:H10 and in the name box above the header of column A type Holidays
and press enter
Now your first formula should work. Of course you can leave the range empty
and add the dates later
but once you have named the range you shouldn't get a name error anymore as
long as ATP is
installed

--


Regards,


Peo Sjoblom

"gafern" wrote in message
...
No, I don't. I was thinking this formula would be used to identify the
next
workday after a weekend. For example, if something was received 07/30/08,
and is due 10 days from that date, it would be due 08/09/08. However,
since
that date falls on a weekend, I'd want the due date in the spreadsheet to
show up as 08/11/08, the first available work day. I guess I'm using the
wrong formula, or maybe I'm doing something else wrong.

Thanks for your help.

GAF

"Peo Sjoblom" wrote:

Do you have a range named holidays where you put public holidays?


--


Regards,


Peo Sjoblom

"gafern" wrote in message
...
Ron:

Thank you for sharing your knowledge.

I'm running Excel 2003 on Windows XP. I've followed the steps you
described
in this earlier thread, using the =WORKDAY formula. Cell A4 contains
my
original date and has a date format. Cell B3 has the number 10 (for the
number of workdays) and is general format (I've also tried number
format).
The formula of =WORKDAY(A4+B3-1,1,holidays) is in cell B4 and that had
a
date
format as well. I am getting the #NAME? error. I have the Analysis
ToolPak
add-in showing in Tools.

Can you think of something I may be doing wrong?

Thanks, in advance, for your consideration.

GAF

"Ron Rosenfeld" wrote:

On Thu, 8 Feb 2007 13:44:00 -0800, Dolt

wrote:

I don't know understand why it works but it does
Thanks


You're welcome.

How it works:

1. Take original date
2. Add one less than the required number of days
3. Then add one Workday.

If adding one workday results in a weekend day or holiday, the Workday
function
will skip over that.


--ron