Workday function linked in an argument of If formula.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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:)
 
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)
 
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
 
Hello, I need your expertise as I am truly struggling to find the formula for “next week 1st working day”. Currently I am using this formula and it works great where every result returned on Monday. However, on these two dates in Cell B2 & B4 are Public Holidays so it is not going to be our 1st working day of the week. Is there a formula to include Public Holiday? I already have Public Holidays table set up.

In this case, the Desire result for cell B2 should be 01-Sep-2015 and B4 should be 26-Jan-2016.
Workdays.webp
 

Attachments

  • Workdays.webp
    Workdays.webp
    18.6 KB · Views: 281
Back
Top