Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Randy
 
Posts: n/a
Default Due Date Calculation?

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

  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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



  #3   Report Post  
Bernard Liengme
 
Posts: n/a
Default

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



  #4   Report Post  
Randy
 
Posts: n/a
Default

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?

  #5   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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?





  #6   Report Post  
Randy
 
Posts: n/a
Default

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.

  #7   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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.



  #8   Report Post  
Randy
 
Posts: n/a
Default

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 =3D OutDate + DaysOut
TestDate:
DayIncreased =3D False
While Weekday(DueDate, vbMonday) 5
DueDate =3D DueDate + 1
DayIncreased =3D True
Wend


myRet =3D Application.Match(CLng(CDate(D=ADueDate)), Holidays, False)


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


If DayIncreased Then GoTo TestDate:=20


End Function

  #9   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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



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



  #10   Report Post  
Randy
 
Posts: n/a
Default

I am getting a compile error when I put the code in and try to run
program.



  #11   Report Post  
Randy
 
Posts: n/a
Default

I am getting a compile error when I put the code in and try to run
program.

  #12   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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


"Randy" wrote in message
oups.com...
I am getting a compile error when I put the code in and try to run
program.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I create a schedule from a list of dates ? Gavin Morris Charts and Charting in Excel 2 October 28th 10 04:08 PM
Date calculation USCBrad Excel Worksheet Functions 3 June 21st 05 04:03 PM
date calculation issues mike richard Excel Discussion (Misc queries) 3 June 14th 05 02:59 AM
date calculation issues mike richard Excel Worksheet Functions 1 June 13th 05 08:12 PM
Date Calculation Mukund Excel Worksheet Functions 3 January 11th 05 12:29 PM


All times are GMT +1. The time now is 11:23 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"