Solved

Business Days

Posted on 2014-10-30
14
107 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now