Adding Day to find a next weekday

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