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




"Randy" wrote in message
oups.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...........

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


"Randy" wrote in message
ups.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?

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


"Randy" wrote in message
ups.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.

Not tested with more that set of data
=(A3+10)+(WEEKDAY(A3+10)=1)+(WEEKDAY(A3+10)=7)*2+N OT(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" wrote in message
oups.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...........