Add 6 months then 60 days

Experts,

How would I add 6 months and then another 60 days to A1 but if the answer is on the weekend then move it a day before?  

thank you
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.

pdvsaProject financeAuthor Commented:
but I also need the formula to acknowledge my named range "Holidays_US_Jap" meaning that if the date falls on a holiday then move it to the preceding workday.
0
barry houdiniCommented:
You can use EDATE to add 6 months then just +60 to get 60 more days, i.e.

=EDATE(A1,6)+60

....and if you then want that date to be moved to the preceding workday if a holiday or weekend date you can use WORKDAY, i.e.

=WORKDAY(EDATE(A1,6)+60+1,-1,Holidays_US_Jap)

regards, barry
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
Martin LissOlder than dirtCommented:
This UDF will give you the previous workday taking your holidays into consideration. I'll continue working on the rest.

Function PreviousWorkday(r As Range) As Date
PreviousWorkday = WorksheetFunction.WorkDay(r, -1, Range("Holidays_US_Jap"))
End Function

Open in new window

0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Martin LissOlder than dirtCommented:
Looks like Barry beat me to it.
0
Martin LissOlder than dirtCommented:
Perhaps for partial credit...

Function PreviousWorkday(r As Range) As Date

PreviousWorkday = WorksheetFunction.EDate(r, 6) + 60
PreviousWorkday = WorksheetFunction.WorkDay(PreviousWorkday, -1, Range("Holidays_US_Jap"))
End Function

Open in new window


Usage:
=PreviousWorkday(A1)
0
pdvsaProject financeAuthor Commented:
Thank you Barry, Martin

Is a holiday part of the count or is the holiday sort of the same as if the answer falls on a holiday then use the preceding workday?
hope that makes sense.  

Ok I see that adding 6 months is different than adding 180 days.  
Using the Edate A1,6 gives me a different date than just simply adding 180 days.  I think Edate is more accurate.    

anyways, I think I am good.  I am getting sick of these date formula questions I am asking!  Its a good schooling though.
0
Martin LissOlder than dirtCommented:
The dates in your named range are treated as if they are weekend days.
0
pdvsaProject financeAuthor Commented:
Martin, I am not sure if the function is designed to be dragged down hundreds of rows and refer to the cell above it.    

I believe Barry's formula, if not mistaken, appears to work if dragged down.
0
pdvsaProject financeAuthor Commented:
please see attached  
there is a 1 day difference between Barry and Martins.
Barry-Martin.xlsm
0
pdvsaProject financeAuthor Commented:
its a macro file because I have the function....nothing else.  I thought would make that point.
0
Martin LissOlder than dirtCommented:
It looks to me like Barry's formula is adding an extra day which I've bolded below.

=WORKDAY(EDATE(A1,6)+60+1,-1,Holidays_US_Jap)

But we both may be wrong. Let me do some more checking
0
barry houdiniCommented:
My version adds 6 months and then 60 days and will give you exactly that result if the resultant date is already a weekday which isn't a holiday (the +1 that Martin mentions above is cancelled out by the -1 in the WORKDAY function) , it only gives you the previous workday is the first result isn't already a working day.

Martin's UDF is giving you the previous workday in all cases, hence the discrepancies.

Either result may be valid, depending on your requirement

regards, barry
0
Martin LissOlder than dirtCommented:
Barry it looks like you are correct but can you explain the +1 in your formula?
0
barry houdiniCommented:
Hello Martin,

The +1 is to "counteract" the -1 in the WORKDAY function

For example:

I assume that if the A1+6 months + 60 days gives a Thursday then that date is the required result (the Thursday) but that if A1+6 months + 60 days gives a Saturday then the previous Friday is the required result. My suggested formula achieves both of those outcomes.

If A1+ 6 months + 60 days is a Thursday then the +1 gives you the next day (the Friday) but WORKDAY will then give you the previous working day (back to Thursday)

If A1+ 6 months + 60 days gives you a Saturday then the +1 gives you the Sunday...and the WORKDAY function gives you the previous workday again (this time the Friday) as required.

This should work in all cases, whether A1+ 6 months + 60 days results in a weekday, a weekend or a holiday....or when there are other holidays involved.

regards, barry
0
pdvsaProject financeAuthor Commented:
Thank you Barry.  
Martin, thank you as well for asking for an explanation.  Barry was gracious enough to tell us how to do a magic trick!   It makes a little more sense now.
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.