Workday function linked in an argument of If formula.

D

dribler2

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
D

dribler2

Ron Rosenfeld said:
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
Click to expand...

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
 
D

dribler2

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
 
R

Ron Rosenfeld

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
Click to expand...


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
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
D

dribler2

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
 
R

Ron Rosenfeld

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.
Click to expand...

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.
Click to expand...

See my response to your previous excerpt.
Is there any link page u can suggest where I can find some explanation or
add-ins ?
Click to expand...

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


--ron
 
D

dribler2

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
Click to expand...

i can read short formula.

happy holidays hohoho
driller
 
R

Ron Rosenfeld

Thanks for the help.

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


i can read short formula.

happy holidays hohoho
driller
Click to expand...

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
 
D

dribler2

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:)
 
D

daddylonglegs

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)
 
D

dribler2

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Workday function not working 6
Calculate next workday after adding calendar days to date in cell 13
WORKDAY 8
Count the remaining weekdays in a data range excluding holidays and start/end dates 5
Using workday function to populate schedule 2
Nested formula using WORKDAY function 5
Excel inventory Sheet for workday month 1
No of Days between two dates and offset formula 4

Top