pdvsa
asked on
Business Days
Hello, how can I change the below to only business days?
=EDATE(I34,CHOOSE(MATCH(D3 5,{"annual ly","quart erly","sem i-annually ","monthly "},0),12,3 ,6,1))
thank you
=EDATE(I34,CHOOSE(MATCH(D3
thank you
Firstly, define EXACTLY "Business Days"
If by "business days" you mean to avoid weekends, then:
=WORKDAY(WORKDAY(EDATE(I34 ,CHOOSE(MA TCH(D35,{" annually", "quarterly ","semi-an nually","m onthly"},0 ),12,3,6,1 )).-1),1)
=WORKDAY(WORKDAY(EDATE(I34
If you mean something more complex, then WORKDAY allows you to define a holiday schedule as a third argument. If this was stored in tblHoliday, then it would be:
=WORKDAY(WORKDAY(EDATE(I34 ,CHOOSE(MA TCH(D35,{" annually", "quarterly ","semi-an nually","m onthly"},0 ),12,3,6,1 )).-1,tblH oliday),1, tblHoliday )
You can find more about this function here: http://office.microsoft.com/en-gb/excel-help/workday-HP005209339.aspx
=WORKDAY(WORKDAY(EDATE(I34
You can find more about this function here: http://office.microsoft.com/en-gb/excel-help/workday-HP005209339.aspx
Hello Phillip,
Assuming your interpretation is correct then your first formula works for me, but you don't really need the double WORKDAY, you should get the same result using this version:
=WORKDAY(EDATE(I34,CHOOSE( MATCH(D35, {"annually ","quarter ly","semi- annually", "monthly"} ,0),12,3,6 ,1))-1,1)
When the original formula gives you a weekend date that will jump to the next working day
regards, barry
Assuming your interpretation is correct then your first formula works for me, but you don't really need the double WORKDAY, you should get the same result using this version:
=WORKDAY(EDATE(I34,CHOOSE(
When the original formula gives you a weekend date that will jump to the next working day
regards, barry
ASKER
Hello and thanks for the comments. I would like to include that added functionality of tblHoliday. I actualy do have this table as a sheet in my file.
assuming barry is correct (shame on me), how can I modify to include the tblHoliday?
assuming barry is correct (shame on me), how can I modify to include the tblHoliday?
Put in
,tblHoliday
Before the last bracket.
However, won't Barry's formula move you one day forward even if it isn't a holiday? I'm away from my computer at the moment, but I though that was the reason I put the double WORKDAY function.
,tblHoliday
Before the last bracket.
However, won't Barry's formula move you one day forward even if it isn't a holiday? I'm away from my computer at the moment, but I though that was the reason I put the double WORKDAY function.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello...do you happen to know why I would get a #Name? how would the holiday tab need to be setup? I mean doesnt the formula need to look at a certain column/named range?
=WORKDAY(EDATE(H66,CHOOSE( MATCH(D67, {"annually ","quarter ly","semi- annually", "monthly"} ,0),12,3,6 ,1))-1,1,holidays-us)
=WORKDAY(EDATE(H66,CHOOSE(
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok that makes sense. I will so when get back to computer. I assume I only highlight the data and not any column names and all the data must be in 1 column. thanks Philip.
ASKER
OK I think I got it. One follow up question and I think it pretty easy but I dont know for sure what I am doing so I better ask. Does the bolded 10 below mean 10 days after? I have to modify the formula for 10 days after the end of the period whether it be annually","quarterly","sem i-annually ","monthly ". The original formula had a 1 and I changed it to 10 but I dont know fi that somehow changes things not intended. thank you
=WORKDAY(EDATE(H34,CHOOSE( MATCH(D35, {"annually ","quarter ly","semi- annually", "monthly"} ,0),12,3,6 ,1))-1,10,Holidays_US_Jap)
=WORKDAY(EDATE(H34,CHOOSE(
ASKER
I think I will need to ask a follow up to this.
I have a lot of manual work to do to incorporate this formula to hundreds of rows. I am going to ask if a function can be used somehow to apply workdate and the holidays to a column. thank you.
I have a lot of manual work to do to incorporate this formula to hundreds of rows. I am going to ask if a function can be used somehow to apply workdate and the holidays to a column. thank you.
That will be 10 work days after the previously calculate date.
Don't understand your last post.
Don't understand your last post.
ASKER
Ok thank you for the clarification in that...