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)
###### 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.

Director, 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?
Project 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
Project 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
Director, 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
``````

Experts Exchange Solution brought to you by