Solved

Add 6 months then 60 days

Posted on 2014-12-27
15
73 Views
Last Modified: 2014-12-28
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
0
Comment
Question by:pdvsa
  • 6
  • 6
  • 3
15 Comments
 

Author Comment

by:pdvsa
ID: 40519962
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 40519976
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40519979
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40519982
Looks like Barry beat me to it.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40519993
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
 

Author Comment

by:pdvsa
ID: 40520005
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40520008
The dates in your named range are treated as if they are weekend days.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:pdvsa
ID: 40520012
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
 

Author Comment

by:pdvsa
ID: 40520018
please see attached  
there is a 1 day difference between Barry and Martins.
Barry-Martin.xlsm
0
 

Author Comment

by:pdvsa
ID: 40520022
its a macro file because I have the function....nothing else.  I thought would make that point.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40520052
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 40520056
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40520059
Barry it looks like you are correct but can you explain the +1 in your formula?
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 500 total points
ID: 40520086
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
 

Author Closing Comment

by:pdvsa
ID: 40520631
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

914 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now