Due Date Calculation?

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

I need a formula that will count add 10 days to a date (A1), but if the
date falls on a weekend or holiday I need the date to calulate the next
workday.

Weekend=Sat,Sunday

Holidays will be in cells J2:J30

Thanks for any help...........
 
Randy,

You will need to use a User-Defined-Function to do this, since you could have a situation where the
due date falls on a weekend, and the whole next week is a holiday, requiring that the due date be
pushed 9 days altogether.

Copy the code below into a standard codemodule, then use it like this, for a date in cell A1

=DueDate(A1,10,J2:J30)

HTH,
Bernie
MS Excel MVP


Function DueDate(OutDate As Date, _
DaysOut As Integer, _
Holidays As Range)
Dim myRet As Variant
Dim DayIncreased As Boolean

DueDate = OutDate + DaysOut
TestDate:
DayIncreased = False
While Weekday(DueDate, vbMonday) > 5
DueDate = DueDate + 1
DayIncreased = True
Wend

myRet = Application.Match(CLng(CDate(DueDate)), Holidays, False)

While Not IsError(myRet)
DueDate = DueDate + 1
DayIncreased = True
myRet = Application.Match(CLng(CDate(DueDate)), Holidays, False)
Wend

If DayIncreased Then GoTo TestDate:

End Function
 
Not tested with more that set of data
=(A3+10)+(WEEKDAY(A3+10)=1)+(WEEKDAY(A3+10)=7)*2+NOT(ISNA(MATCH((A3+10)+(WEEKDAY(A3+10)=1)+(WEEKDAY(A3+10)=7)*2,J2:J30,0)))

term 1 (A3+10) adds 10 days
term 2 (weekday....) checks for Sunday (adds a day)
term 3 (weekday..) checks for Saturday (adds 2 days)
term 4 checks if new day is holiday (adds a day)
Formula will fail is due day lands on a day-1 of a multiday holiday period!

best wishes
 
Bernie

I put your code in Module and the formula in a cell. It gives me a
calculation fine, but when I put in 06/23/05 it returns 07/04/05 which
is a Monday and a holiday in the date range that was designated. Is
there a way to get it to skip the weekend and the holiday?
 
Randy,

When I enter 6/23/05, I get 7/5/5 as the due date. Are you sure that the date entered in J2:J30 is
actually 7/4/5, and not 7/4/some other year, just formatted to show the month and date?

HTH,
Bernie
MS Excel MVP
 
Bernie

Thank you very much. There was a miscopy in the code that I found that
was causing the problem. This is a Due Date Program my staff have been
using in the Food Stamp Program to calculate due dates and has been
used for 10 years in Lotus 123. You were a big help in helping me
convert it to Microsoft Excel.

Thanks again.
 
Randy,

Glad to be of help, and thanks for letting me know that you got it to work. Feedback (especially
good news) is always appreciated.

Bernie
MS Excel MVP
 
Bernie I do have one more question about the solution you gave me
below. What would I have to change to have it give me a date before
the weekend and/or holiday rather than after. Some of the tasks
require action before the weekend and/or holiday.

DueDate(A1,10,J2:J30)


HTH,
Bernie
MS Excel MVP


Function DueDate(OutDate As Date, _
DaysOut As Integer, _
Holidays As Range)
Dim myRet As Variant
Dim DayIncreased As Boolean


DueDate = OutDate + DaysOut
TestDate:
DayIncreased = False
While Weekday(DueDate, vbMonday) > 5
DueDate = DueDate + 1
DayIncreased = True
Wend


myRet = Application.Match(CLng(CDate(D­ueDate)), Holidays, False)


While Not IsError(myRet)
DueDate = DueDate + 1
DayIncreased = True
myRet = Application.Match(CLng(CDate(D­ueDate)), Holidays, False)
Wend


If DayIncreased Then GoTo TestDate:


End Function
 
Randy,

You would need to modify the function to accept a fourth parameter: see the code below.

Then use it like this to move to a later date:

=DueDate(A1,10,J2:J30,TRUE)

or to move earlier date:

=DueDate(A1,10,J2:J30,FALSE)

or to have another cell value control it

=DueDate(A1,10,J2:J30,A2="Later")

(moves to a later date if cell A1 contains the string Later, earlier if it contains anything else)

HTH,
Bernie
MS Excel MVP

Function DueDate(OutDate As Date, _
DaysOut As Integer, _
Holidays As Range, _
Increase As Boolean)
Dim myRet As Variant
Dim DayChanged As Boolean

DueDate = OutDate + DaysOut
TestDate:
DayChanged = False
While Weekday(DueDate, vbMonday) > 5
DueDate = DueDate + IIf(Increase, 1, -1)
DayChanged = True
Wend

myRet = Application.Match(CLng(CDate(DueDate)), Holidays, False)

While Not IsError(myRet)
DueDate = DueDate + IIf(Increase, 1, -1)
DayChanged = True
myRet = Application.Match(CLng(CDate(DueDate)), Holidays, False)
Wend

If DayChanged Then GoTo TestDate:

End Function



Bernie I do have one more question about the solution you gave me
below. What would I have to change to have it give me a date before
the weekend and/or holiday rather than after. Some of the tasks
require action before the weekend and/or holiday.

DueDate(A1,10,J2:J30)
 
Randy,

I get no errors - I tested it fully.

Did you delete the old function from the codemodule? Did you put the code into a standard codemodule
and not a worksheet's codemodule?

How are you calling the function from the worksheet? (copy and paste your formula....)

HTH,
Bernie
MS Excel MVP
 
Back
Top