• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

workday issues

D

dhanush

New Member
Hi there,


I have workday(1/11/11,0) -> I should get 1/11/11. This seems to work.


Now I do workday(1/11/11,0,{1/11/11,1/12/11}) I expect to get 1/13/11 (assuming 13th is not sat/sunday). But I do not believe Excel is skipping to the day past the holiday calendars.


Is this a known issue in Excel or am i doing something wrong.


Regards
 
a few points


workday(1/11/11,0,{1/11/11,1/12/11}) you are saying 0 days after 1/11 so that will be 1/11

if you had done

workday(1/11/11,1,{1/11/11,1/12/11}) you are saying 1 days after 1/11 so that will be 1/13 allowing for the holidays


I would try not to use dates as you are using them


Preferred way is either

workday(date(2011,1,11),0) or workday(A10,0) where A10 has a valid date in it

similarly for the holidays workday(A10,0, B1:B10) where B1:B10 are a list of holidays
 
Thanks Hui,


I should have written better:


I did have the following:


A1 = "01/01/11"


B1:B2 = 01/01/11 and 01/02/11


C1 = workday(a1,0,b1:b2)


I do not believe c1 gets the valid date beyond 01/02/11.


sorry for the bad post earlier.


regards
 
You have A1 as text not as a number

If you put " around it will be text

Just enter 1/1/2011
 
Thanks Hui,


I apologize, yes it is entered as 1/1/11 and not as text in excel. I know I am doing something silly here - my sincere apologies for taking your time here.


**All dates entered as date**


2/11/2011 FRIDAY


02/14/11 WORKDAY(1 DAY)

02/11/11 WORKDAY(0DAY)


Holidays

02/11/11

02/14/11


02/11/11 workday(a1,0,b9:b10)


I would expect 2/15/11
 
X plus 0 is X, it doesn't make sense for it not to be

Even though X is a holiday
 
Hui,


how would do such a check:


I am entering start date and effective date for set of items. I would like to ensure that both startdate and enddate are valid business days (for given region). I was hoping to show the my original entry and the adjusted entry i,e the bumped date next to it. I was hoping to use workday as a way to do this. any ideas, I can do VBA, but would only do it if it cannot be done via a existing excel function.


regards
 
In addition to Hui's reply read this again on ms site:


>>

WORKDAY(start_date,days,holidays)


Start_date is a date that represents the start date.


Days is the number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.

<<


Days : nonweekend and nonholidays.


-regards
 
@Dhanush

Try the following

=IF(OR(WEEKDAY(A1,2)>5,IFERROR(MATCH(A1,$B$1:$B$2,0),FALSE)),WORKDAY(A1,1,$B$1:$B$2),A1)
 
You must log in or register to reply here.
Back
Top