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

x
Solved

Posted on 2014-10-30
Medium Priority
130 Views
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
Question by:pdvsa
• 6
• 5
• 2
• +1

LVL 37

Expert Comment

ID: 40413723
0

LVL 24

Expert Comment

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

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)

0

LVL 50

Expert Comment

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

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

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

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

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

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

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

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

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

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

0

Author Comment

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

## Featured Post

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â€¦
###### Suggested Courses
Course of the Month13 days, 1 hour left to enroll