Business Days andHolidays Function

hello,

I have rows of data and have a formula as below but mos tof the formulas do not have the part about WORKDAY and HOLIDAYS as shown below.  It will take me a long time to manually change the formulas to add the WORKDAY and HOLIDAY (named ranged of HOliday_US_Jap)

I imagine there is a function I can use to apply to a certain column (ie column H has all the dates).  Basically, if the column of dates contains either  date on the weekend or is a date that is a HOLIDAY then move forward to the next available day that is a weekday and is not a holiday.  Hope that makes sense.  I cant simply use a find and replace as the formulas are vastly different.  I was only adding the WORKDAY and HOLIDAY parts.
thak you

here is the formula.  I have modified with the bold
=WORKDAY(EDATE(H34,CHOOSE(MATCH(D35,{"annually","quarterly","semi-annually","monthly"},0),12,3,6,1))-1,10,Holidays_US_Jap)
pdvsaProject financeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I presume what you are asking is, how to add an extra column with this outer bit of the formula.

Sounds like the answer should be

=Workday(h2,10,Holidays_US_Jap)

Where h2 is your current formula.

Or am I missing something?
0
pdvsaProject financeAuthor Commented:
Philip, actually that is jot what I am after.  The formula is different for many rows.  I might be able to get it to work but what I am really after is code that will update the column for weekday and holiday.  

Let me know what you think.  

Thank you
0
pdvsaProject financeAuthor Commented:
Philip, is it clear what I am trying to do?  Basically, instead of putting the WEEDAY and HOLIDAY function within the cell, I would like to be able to apply it by code such as within a function (hit Alt F11 and the code shows)

thank you
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Do you mean this (make sure your spreadsheet is saved before running this code):

Sub ExpandFormula()
For introw = 1 To 10000
    If Cells(introw, 8).Formula <> "" Then
        Cells(introw, 8).Formula = "=Workday(" & Mid(Cells(introw, 8).Formula, 2, 9999) & ",Holidays_US_Jap)"
    End If
Next
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pdvsaProject financeAuthor Commented:
Phillip, thank you.  I think I will have to do it manually though because I have some formulas that already have the WORDAYS function.  No problem, I can do manually.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.