pdvsa
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Looks like Barry beat me to it.
Perhaps for partial credit...
Usage:
=PreviousWorkday(A1)
Function PreviousWorkday(r As Range) As Date
PreviousWorkday = WorksheetFunction.EDate(r, 6) + 60
PreviousWorkday = WorksheetFunction.WorkDay(PreviousWorkday, -1, Range("Holidays_US_Jap"))
End Function
Usage:
=PreviousWorkday(A1)
ASKER
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.
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.
The dates in your named range are treated as if they are weekend days.
ASKER
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.
I believe Barry's formula, if not mistaken, appears to work if dragged down.
ASKER
ASKER
its a macro file because I have the function....nothing else. I thought would make that point.
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
=WORKDAY(EDATE(A1,6)+60+1,-1,Holidays_US_Jap)
But we both may be wrong. Let me do some more checking
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
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
Barry it looks like you are correct but can you explain the +1 in your formula?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER