Date problem--still can't deal with holidays---to Aladin
Posted by Thomas on November 01, 2001 6:35 AM
Thanks for helping me develop this formula.
But the first workday function doesn't deal with holiday issue. For instance, A1 is 11/6/01, B1 is 16, I want to know the result shows the date of 16 days later. If it falls on the weekend or holiday.It will automatically post pone to the following business day. In this case, the result falls on 11/22/01 which is a holiday(Thanksgiving,Thursday). How do I modify the foumula that will shows the result as a right answer? Thanks?
Thomas
=WORKDAY(DATE(YEAR(A1),MONTH(A1),DAY(A1)+$B$1),0,HOLIDAYS)+IF(WEEKDAY(WORKDAY(DATE(YEAR(A1),MONTH(A1),DAY(A1)+$B$1),0,HOLIDAYS),2)=6,2,IF(WEEKDAY(WORKDAY(DATE(YEAR(A1),MONTH(A1),DAY(A1)+$B$1),0,HOLIDAYS),2)=7,1,0))
P.S. It seems if we use '0' as a variable, the formula doesn't work for workday function.
Posted by Todd on November 01, 2001 6:57 AM
Maybe I can help some..
I ran into the same problem when you use 0. Instead, in the first portion, replace
)+$B$1),0,
with
)+$B$1)-1,1,
furthermore, when you have HOLIDAYS, is that a range on your sheet containing holidays in it? I believe if you omit this portion of the function, excel uses an automatic list of holidays, which probably includes thanksgiving.
Posted by Thomas on November 01, 2001 7:11 AM
This solve the 11/6/01 problem.and I have range for all holidays. But this formula went wrong for all the thursday and friday calculation. For instance, if start date is 11/8/01, it will falls on 11/28/01.If begin on 11/9/01, it falls on 11/27/01. same result for 11/15/01 and 11/16/01. Doesn't make sence. Need further help. Thanks.
Thomas
Posted by Todd on November 01, 2001 8:48 AM
I think its because the WORKDAY function already advances past friday and saturday.. so you don't need any of your function past the + sign.... so the function was adding 16 to 8, getting 24, then advancing to monday (26) then adding 2 for saturday, to 28.
sorry i didn't catch that the first time :) This solve the 11/6/01 problem.and I have range for all holidays. But this formula went wrong for all the thursday and friday calculation. For instance, if start date is 11/8/01, it will falls on 11/28/01.If begin on 11/9/01, it falls on 11/27/01. same result for 11/15/01 and 11/16/01. Doesn't make sence. Need further help. Thanks.
Posted by Thomas on November 01, 2001 9:22 AM
it works this time
you know what, I try to replace all the portion with
)+$B$1)-1,1,
so the foumula now is
=WORKDAY(DATE(YEAR(A1),MONTH(A1),DAY(A1)+$B$1)-1,1,HOLIDAYS)+IF(WEEKDAY(WORKDAY(DATE(YEAR(A1),MONTH(A1),DAY(A1)+$B$1)-1,1,HOLIDAYS),2)=6,2,IF(WEEKDAY(WORKDAY(DATE(YEAR(A1),MONTH(A1),DAY(A1)+$B$1)-1,1,HOLIDAYS),2)=7,1,0))
and it seems work. I can't explain, but it really falls on the right place.
Could you confirm that?
Thanks.
Thomas I think its because the WORKDAY function already advances past friday and saturday.. so you don't need any of your function past the + sign.... so the function was adding 16 to 8, getting 24, then advancing to monday (26) then adding 2 for saturday, to 28.
Posted by Todd on November 01, 2001 10:28 AM
Re: it works this time
If it works, that's great. I'm skeptical though, because it doesn't seem like it should work. You should only need the first term (everything before the + sign) because the WORKDAY function advances to the next monday if it results in a Sat or Sun.
Posted by Aladin Akyurek on November 01, 2001 12:29 PM
Re: it works this time
As Todd states, the shortened formula
=WORKDAY(DATE(YEAR(A1),MONTH(A1),DAY(A1)+$B$1)-1,1,HOLIDAYS)
is sufficient.
I'm skeptical though, because it doesn't seem like it should work.
I think the +IF doesn't get a chance, because the first part never lands on a Saturday or Sunday.
You should only need the first term (everything before the + sign) because the WORKDAY function advances to the next monday if it results in a Sat or Sun.
Yep.
Aladin
