Check if dates appear in list and take action if it does.

[Flash0220]

Hello,


I'm having the following problem, and i'm hoping that anyone here can help me solve it.

I have a variable (declared as date) called "EndDate".
VBA code will give this variable a (date) value.

After that (new) VBA code has to check if the date stored in the variable "EndDate"
appears in a list of dates stored at a "tab" called "NoWorkDays" in the range "A7:A47".

Some of the dates in this range are :

January 14 2011
April 25 2011
June 2 2011
June 13 2011
December 26 2011
December 27 2011

An example.

Lets say the date stored in the variable "EndDate" is "June 13 2011".
This date is in the list mentioned above, so i now need VBA code
which will fill this variable with the value "June 14 2011".
Which (in this case) is the next work day.

Another example.

Lets say the date stored in the variable "EndDate" is "December 26 2011".
This date is in the list mentioned above, so i now need VBA code
which will fill this variable with the new value. (The next work day, so no weekends.)
Generating the date "December 27 2011" is wrong, because that date
is also in the list mentioned above.
So the date "December 28 2011" has to be generated.

A last example.

Lets say the date stored in the variable "EndDate" is "January 14 2011".
This date is in the list mentioned above, so i now need VBA code
which will fill this variable with the new value. (The next work day, so no weekends.)
So the date "January 17" has to be generated, because the 15th and 16th of january
are weekend days.

All the examples mentioned above have to calculate the "next" work day.
But in some cases the "previous" work day has to be calculated, so i need
two similar SUB's of VBA code.
One for the "next working day" and one for the "previous working day".

Is there a way to fix this problem ?
I tried to figure out how to solve this problem, but unfortunately i can't fix it.

Who can help me ?
Thanks for helping.


Best regards,
Flash0220

(I'm using the dutch version of Excel 2003.)

 

[drsarao]

The following code will bump up the EndDate taking Noworkdays & weekends:

Sub NextWorkDay()
EndDate = Range("NoWorkdays!A1")
Do
    temp = EndDate 'save initial value
    If Weekday(EndDate) = 7 Then EndDate = EndDate + 2 '?saturday
    If Weekday(EndDate) = 1 Then EndDate = EndDate + 1 '?sunday
    If Not IsError(Application.Match(CLng(EndDate), Range("NoWorkdays!A7:A47"), 0)) Then EndDate = EndDate + 1 '?in list
Loop While EndDate > temp 'did the date change?
MsgBox EndDate
End Sub

Similar logic can be used for PreviousWorkday().
These can in fact be converted to functions for better readability.

 

[Flash0220]

Hello,


Thank you very much for your answer and VBA code.
It works perfectly !


Best regards,
Flash0220

 

[drsarao]

You are welcome.
Did you try PreviousWorkday and conversion to function?