Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Business Days

Posted on 2014-10-30
14
Medium Priority
?
130 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
Independent Software Vendors: 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!

 
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 1000 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 1000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

578 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