In A1 a separate sheet will feed in the number of days. (from "no data", or 0
to 31)
in A2 : = if(A1="no data","x",A1)

in B1 a separate sheet will feed in a date.
in B2 : = WORKDAY(B1,A2)
this B2 is used as the end date of a WORKDAY scheduling.

I happen to run the schedule and have not detected the invalid workday
result of B2.
This happens when
A1 = 0
A2 = 0
B1 = a date that falls on sunday or saturday.
B2 = a date that is printed as a sunday or saturday.

Workday function do not govern the needed result.

do anybody encounter this scenario. Or maybe i am just confused

happy holidays hohoho
dribler2

On Fri, 22 Dec 2006 18:35:00 -0800, dribler2
wrote:

In A1 a separate sheet will feed in the number of days. (from "no data", or 0
to 31)
in A2 : = if(A1="no data","x",A1)

in B1 a separate sheet will feed in a date.
in B2 : = WORKDAY(B1,A2)
this B2 is used as the end date of a WORKDAY scheduling.

I happen to run the schedule and have not detected the invalid workday
result of B2.
This happens when
A1 = 0
A2 = 0
B1 = a date that falls on sunday or saturday.
B2 = a date that is printed as a sunday or saturday.

Workday function do not govern the needed result.

do anybody encounter this scenario. Or maybe i am just confused

happy holidays hohoho
dribler2

Possibly you are confused.

Regardless of the date in B1, the formula in B2 will give the same date as in
B1. "0" workdays added to a given date will not change the given date.

A2: 0
B1: 23 Dec 2006

B2: =WORKDAY(B1,A2)



--ron

"Ron Rosenfeld" wrote:

On Fri, 22 Dec 2006 18:35:00 -0800, dribler2
wrote:

In A1 a separate sheet will feed in the number of days. (from "no data", or 0
to 31)
in A2 : = if(A1="no data","x",A1)

in B1 a separate sheet will feed in a date.
in B2 : = WORKDAY(B1,A2)
this B2 is used as the end date of a WORKDAY scheduling.

I happen to run the schedule and have not detected the invalid workday
result of B2.
This happens when
A1 = 0
A2 = 0
B1 = a date that falls on sunday or saturday.
B2 = a date that is printed as a sunday or saturday.

Workday function do not govern the needed result.

do anybody encounter this scenario. Or maybe i am just confused

happy holidays hohoho
dribler2

Possibly you are confused.

Regardless of the date in B1, the formula in B2 will give the same date as in
B1. "0" workdays added to a given date will not change the given date.

A2: 0
B1: 23 Dec 2006

B2: =WORKDAY(B1,A2)



--ron


You are right Ron about this confusion, if there is a 0 on a start date of
saturday or sunday, the workday function cannot jump towards the monday,
unlike if the start date is a workday , it can jump all over these 2
non-work days.
I still don't get it how come the WORKDAY function can expose a non-working
day RESULT rather simply giving an error remark.
Is there anyway around this to assure the integrity of the result which is
needed as a Real Workday ?

Meaning, from the sampled scenario, if the start date falls on non-workday,
and the 0 day is added, then the Result will be the date on Monday - the Real
WORKDAY.

Thanks Ron,

happy holidays hohoho
driller2

Hi Ron,

Just to clarify why i need a workaround on this seemingly endless scenario.
Below is an extract from my CURRENT Help files

WORKDAY
.........."Use WORKDAY to exclude weekends or holidays when you calculate
invoice due dates, expected delivery times, or the number of days of work
performed."

Remarks

* Microsoft Excel stores dates as sequential serial numbers so they can be
used in calculations. By default, January 1, 1900 is serial number 1, and
January 1, 2008 is serial number 39448 because it is 39,448 days after
January 1, 1900. Microsoft Excel for the Macintosh uses a different date
system as its default.
* If any argument is not a valid date, WORKDAY returns the #VALUE! error
value. -------- ***
* If start_date plus days yields an invalid date, WORKDAY returns the #NUM!
error value. ------***
* If days is not an integer, it is truncated.


maybe I have a wrong interpretation.

happy holidays hohoho
dribler2


"dribler2" wrote:


"Ron Rosenfeld" wrote:

On Fri, 22 Dec 2006 18:35:00 -0800, dribler2
wrote:

In A1 a separate sheet will feed in the number of days. (from "no data", or 0
to 31)
in A2 : = if(A1="no data","x",A1)

in B1 a separate sheet will feed in a date.
in B2 : = WORKDAY(B1,A2)
this B2 is used as the end date of a WORKDAY scheduling.

I happen to run the schedule and have not detected the invalid workday
result of B2.
This happens when
A1 = 0
A2 = 0
B1 = a date that falls on sunday or saturday.
B2 = a date that is printed as a sunday or saturday.

Workday function do not govern the needed result.

do anybody encounter this scenario. Or maybe i am just confused

happy holidays hohoho
dribler2

Possibly you are confused.

Regardless of the date in B1, the formula in B2 will give the same date as in
B1. "0" workdays added to a given date will not change the given date.

A2: 0
B1: 23 Dec 2006

B2: =WORKDAY(B1,A2)



--ron


You are right Ron about this confusion, if there is a 0 on a start date of
saturday or sunday, the workday function cannot jump towards the monday,
unlike if the start date is a workday , it can jump all over these 2
non-work days.
I still don't get it how come the WORKDAY function can expose a non-working
day RESULT rather simply giving an error remark.
Is there anyway around this to assure the integrity of the result which is
needed as a Real Workday ?

Meaning, from the sampled scenario, if the start date falls on non-workday,
and the 0 day is added, then the Result will be the date on Monday - the Real
WORKDAY.

Thanks Ron,

happy holidays hohoho
driller2

On Sat, 23 Dec 2006 06:04:01 -0800, dribler2
wrote:

You are right Ron about this confusion, if there is a 0 on a start date of
saturday or sunday, the workday function cannot jump towards the monday,
unlike if the start date is a workday , it can jump all over these 2
non-work days.
I still don't get it how come the WORKDAY function can expose a non-working
day RESULT rather simply giving an error remark.
Is there anyway around this to assure the integrity of the result which is
needed as a Real Workday ?

Meaning, from the sampled scenario, if the start date falls on non-workday,
and the 0 day is added, then the Result will be the date on Monday - the Real
WORKDAY.

Thanks Ron,

happy holidays hohoho
driller2


What do you want for results in these instances:

Day of week Days to Add
Sat 0
Sat 2
Mon 0
Mon 2
Fri 0
Fri 2


--ron

On Sat, 23 Dec 2006 07:35:01 -0800, dribler2
wrote:

Hi Ron,

Just to clarify why i need a workaround on this seemingly endless scenario.
Below is an extract from my CURRENT Help files

WORKDAY
........."Use WORKDAY to exclude weekends or holidays when you calculate
invoice due dates, expected delivery times, or the number of days of work
performed."

Remarks

* Microsoft Excel stores dates as sequential serial numbers so they can be
used in calculations. By default, January 1, 1900 is serial number 1, and
January 1, 2008 is serial number 39448 because it is 39,448 days after
January 1, 1900. Microsoft Excel for the Macintosh uses a different date
system as its default.
* If any argument is not a valid date, WORKDAY returns the #VALUE! error
value. -------- ***
* If start_date plus days yields an invalid date, WORKDAY returns the #NUM!
error value. ------***
* If days is not an integer, it is truncated.


maybe I have a wrong interpretation.


An "invalid" date is an entry that does not translate to an Excel date; it has
nothing to do with the function being used and is dependent upon, among other
things, the Windows regional settings. For example, with US settings,
31/12/2005 would not be a valid date.
--ron

Thanks Ron,

"What do you want for results:"

A work around ! Maybe a revised formula.

Simply a result that must fall on the succeeding WORKDAY, as I understood
from the function's suggested usage vs. intended result.

Now, please clarify to me if I am still confused based on my previous excerpt.

Is there any link page u can suggest where I can find some explanation or
add-ins ?
thanks again.
happy holidays hohoho
dribler2


"Ron Rosenfeld" wrote:

On Sat, 23 Dec 2006 06:04:01 -0800, dribler2
wrote:

You are right Ron about this confusion, if there is a 0 on a start date of
saturday or sunday, the workday function cannot jump towards the monday,
unlike if the start date is a workday , it can jump all over these 2
non-work days.
I still don't get it how come the WORKDAY function can expose a non-working
day RESULT rather simply giving an error remark.
Is there anyway around this to assure the integrity of the result which is
needed as a Real Workday ?

Meaning, from the sampled scenario, if the start date falls on non-workday,
and the 0 day is added, then the Result will be the date on Monday - the Real
WORKDAY.

Thanks Ron,

happy holidays hohoho
driller2


What do you want for results in these instances:

Day of week Days to Add
Sat 0
Sat 2
Mon 0
Mon 2
Fri 0
Fri 2


--ron

On Sat, 23 Dec 2006 08:06:01 -0800, dribler2
wrote:

Thanks Ron,

"What do you want for results:"

A work around ! Maybe a revised formula.

Simply a result that must fall on the succeeding WORKDAY, as I understood
from the function's suggested usage vs. intended result.

I need that you precisely answer the question I posed, and then it may be
possible to provide you with a formula. Perhaps by adding a third column, my
question will be more clear:

Day of week Days to Add WeekDay of the Result
Sat 0 ???
Sat 2 ???
Mon 0 ???
Mon 2 ???
Fri 0 ???
Fri 2 ???

If you could return the above, with the ??? replaced by the day of the week you
wish the formula to return, I might understand what you are trying to
accomplish.




Now, please clarify to me if I am still confused based on my previous excerpt.

See my response to your previous excerpt.


Is there any link page u can suggest where I can find some explanation or
add-ins ?

support.microsoft.com and ask your questions of the knowledge base.


--ron

Thanks for the help.

leaving disputes on Workday() function, another formula may fit me based on
your table below.

Day of week Days to Add WeekDay of the Result
Sat 0 next Mon
Sat 2 next Tues
Mon 0 Mon
Mon 2 Wed
Fri 0 Fri
Fri 2 Tues

i can read short formula.

happy holidays hohoho
driller

"Ron Rosenfeld" wrote:

On Sat, 23 Dec 2006 08:06:01 -0800, dribler2
wrote:

Thanks Ron,

"What do you want for results:"

A work around ! Maybe a revised formula.

Simply a result that must fall on the succeeding WORKDAY, as I understood
from the function's suggested usage vs. intended result.

I need that you precisely answer the question I posed, and then it may be
possible to provide you with a formula. Perhaps by adding a third column, my
question will be more clear:

Day of week Days to Add WeekDay of the Result
Sat 0 ???
Sat 2 ???
Mon 0 ???
Mon 2 ???
Fri 0 ???
Fri 2 ???

If you could return the above, with the ??? replaced by the day of the week you
wish the formula to return, I might understand what you are trying to
accomplish.




Now, please clarify to me if I am still confused based on my previous excerpt.

See my response to your previous excerpt.


Is there any link page u can suggest where I can find some explanation or
add-ins ?

support.microsoft.com and ask your questions of the knowledge base.


--ron

On Sat, 23 Dec 2006 11:46:01 -0800, dribler2
wrote:

Thanks for the help.

leaving disputes on Workday() function, another formula may fit me based on
your table below.

Day of week Days to Add WeekDay of the Result
Sat 0 next Mon
Sat 2 next Tues
Mon 0 Mon
Mon 2 Wed
Fri 0 Fri
Fri 2 Tues

i can read short formula.

happy holidays hohoho
driller

Well, you set up some special circumstances which need to be accounted for.

For example


Sat `0 Next Monday
Sat 2 Next Tues

implies
Sat 1 next Monday

So there are two different "days to add" to the same date that result in the
same result.

And also "days to add" has a different meaning, in your requirements, depending
on whether or not the "base" is a weekday.

Here's one way:

=WORKDAY(A1,B1+(WEEKDAY(WORKDAY(A1,B1),2)=6))

and possibly (not thoroughly checked):

=WORKDAY(A1,B1+(WEEKDAY(WORKDAY(A1,B1,Holidays),2) =6),Holidays)





--ron

your formula works for my application,
=WORKDAY(A1,B1+(WEEKDAY(WORKDAY(A1,B1),2)=6))

thanks a lot, with the above, i get rid of being trapped on a non-wokday
result for the crucial end date of my schedule.

wish more reply from you..

happy holidays hohoho
driller:)


"Ron Rosenfeld" wrote:

On Sat, 23 Dec 2006 11:46:01 -0800, dribler2
wrote:

Thanks for the help.

leaving disputes on Workday() function, another formula may fit me based on
your table below.

Day of week Days to Add WeekDay of the Result
Sat 0 next Mon
Sat 2 next Tues
Mon 0 Mon
Mon 2 Wed
Fri 0 Fri
Fri 2 Tues

i can read short formula.

happy holidays hohoho
driller

Well, you set up some special circumstances which need to be accounted for.

For example


Sat `0 Next Monday
Sat 2 Next Tues

implies
Sat 1 next Monday

So there are two different "days to add" to the same date that result in the
same result.

And also "days to add" has a different meaning, in your requirements, depending
on whether or not the "base" is a weekday.

Here's one way:

=WORKDAY(A1,B1+(WEEKDAY(WORKDAY(A1,B1),2)=6))

and possibly (not thoroughly checked):

=WORKDAY(A1,B1+(WEEKDAY(WORKDAY(A1,B1,Holidays),2) =6),Holidays)





--ron

I don't think the holiday option will work, Ron

An alternative for the fisrt formula is

=WORKDAY(A1,B1+((NETWORKDAYS(A1,A1)+B1)=0))

which you can extend to

=WORKDAY(A1,B1+((NETWORKDAYS(A1,A1,holidays)+B1)=0 ),holidays)



"Ron Rosenfeld" wrote:

On Sat, 23 Dec 2006 11:46:01 -0800, dribler2
wrote:

Thanks for the help.

leaving disputes on Workday() function, another formula may fit me based on
your table below.

Day of week Days to Add WeekDay of the Result
Sat 0 next Mon
Sat 2 next Tues
Mon 0 Mon
Mon 2 Wed
Fri 0 Fri
Fri 2 Tues

i can read short formula.

happy holidays hohoho
driller

Well, you set up some special circumstances which need to be accounted for.

For example


Sat `0 Next Monday
Sat 2 Next Tues

implies
Sat 1 next Monday

So there are two different "days to add" to the same date that result in the
same result.

And also "days to add" has a different meaning, in your requirements, depending
on whether or not the "base" is a weekday.

Here's one way:

=WORKDAY(A1,B1+(WEEKDAY(WORKDAY(A1,B1),2)=6))

and possibly (not thoroughly checked):

=WORKDAY(A1,B1+(WEEKDAY(WORKDAY(A1,B1,Holidays),2) =6),Holidays)





--ron

thanks for participating

i guess you are right to extend the first formula to accredit the
non-working holidays, please differentiate by example why you don't think
Ron's holiday option will work compared to your suggestion. It will be nice
to be assured with a negating test.

I may blend both suggestions from you and ron.

happy holidays hohoho
driller2

"daddylonglegs" wrote:

I don't think the holiday option will work, Ron

An alternative for the fisrt formula is

=WORKDAY(A1,B1+((NETWORKDAYS(A1,A1)+B1)=0))

which you can extend to

=WORKDAY(A1,B1+((NETWORKDAYS(A1,A1,holidays)+B1)=0 ),holidays)



"Ron Rosenfeld" wrote:

On Sat, 23 Dec 2006 11:46:01 -0800, dribler2
wrote:

Thanks for the help.

leaving disputes on Workday() function, another formula may fit me based on
your table below.

Day of week Days to Add WeekDay of the Result
Sat 0 next Mon
Sat 2 next Tues
Mon 0 Mon
Mon 2 Wed
Fri 0 Fri
Fri 2 Tues

i can read short formula.

happy holidays hohoho
driller

Well, you set up some special circumstances which need to be accounted for.

For example


Sat `0 Next Monday
Sat 2 Next Tues

implies
Sat 1 next Monday

So there are two different "days to add" to the same date that result in the
same result.

And also "days to add" has a different meaning, in your requirements, depending
on whether or not the "base" is a weekday.

Here's one way:

=WORKDAY(A1,B1+(WEEKDAY(WORKDAY(A1,B1),2)=6))

and possibly (not thoroughly checked):

=WORKDAY(A1,B1+(WEEKDAY(WORKDAY(A1,B1,Holidays),2) =6),Holidays)





--ron

On Sat, 23 Dec 2006 13:48:00 -0800, daddylonglegs
wrote:

I don't think the holiday option will work, Ron

That would not surprise me. As I wrote, I had not tested it with any degree of
completeness.
--ron