Solved

Business Days

Posted on 2014-10-30
14
117 Views
Last Modified: 2014-11-01
Hello, how can I change the below to only business days?

=EDATE(I34,CHOOSE(MATCH(D35,{"annually","quarterly","semi-annually","monthly"},0),12,3,6,1))

thank you
0
Comment
Question by:pdvsa
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40413723
Firstly, define EXACTLY "Business Days"
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40413734
If by "business days" you mean to avoid weekends, then:

=WORKDAY(WORKDAY(EDATE(I34,CHOOSE(MATCH(D35,{"annually","quarterly","semi-annually","monthly"},0),12,3,6,1)).-1),1)
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40413737
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(MATCH(D35,{"annually","quarterly","semi-annually","monthly"},0),12,3,6,1)).-1,tblHoliday),1,tblHoliday)

You can find more about this function here: http://office.microsoft.com/en-gb/excel-help/workday-HP005209339.aspx
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 50

Expert Comment

by:barry houdini
ID: 40414383
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","quarterly","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
0
 

Author Comment

by:pdvsa
ID: 40414415
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?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40414501
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.
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 250 total points
ID: 40414572
No, it will give the same as the original date if it's a workday, my formula just subtracts 1 rather than going back one workday, so generically it's

=WORKDAY(DATE-1,1)

which will always give you the first working day on or after DATE, as Phillip says to exclude holidays it's simply

=WORKDAY(EDATE(I34,CHOOSE(MATCH(D35,{"annually","quarterly","semi-annually","monthly"},0),12,3,6,1))-1,1,holidays)
0
 

Author Comment

by:pdvsa
ID: 40416210
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","quarterly","semi-annually","monthly"},0),12,3,6,1))-1,1,holidays-us)
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 250 total points
ID: 40416379
You need a ranged area called holidays-us set up, not a tab.

Highlight the holiday dates, go into the name (the bit on the screen which normally shows the cell reference, like A1), and type holidays-us and (this is important) press Return.
0
 

Author Comment

by:pdvsa
ID: 40416571
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.
0
 

Author Comment

by:pdvsa
ID: 40417459
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","semi-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","quarterly","semi-annually","monthly"},0),12,3,6,1))-1,10,Holidays_US_Jap)
0
 

Author Comment

by:pdvsa
ID: 40417463
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.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40417481
That will be 10 work days after the previously calculate date.

Don't understand your last post.
0
 

Author Comment

by:pdvsa
ID: 40417615
Ok thank you for the clarification in that...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question