[SOLVED] Due Date Calculation?

[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...........

[Bernie Deitrick]

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




"Randy" <ranmcc@msn.com> wrote in message
news:1121099702.771030.230920@g44g2000cwa.googlegroups.com...
>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]

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?

[Bernie Deitrick]

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


"Randy" <ranmcc@msn.com> wrote in message
news:1121273953.083537.40450@g14g2000cwa.googlegroups.com...
> 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]

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.

[Bernie Deitrick]

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


"Randy" <ranmcc@msn.com> wrote in message
news:1121348660.435700.72730@g49g2000cwa.googlegroups.com...
> 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.
>

[Bernard Liengme]

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Randy" <ranmcc@msn.com> wrote in message
news:1121099702.771030.230920@g44g2000cwa.googlegroups.com...
>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...........
>