Solved

Business Days

Posted on 2014-10-30
14
113 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

Question has a verified solution.

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

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…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

825 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