Date input to exclude weekends

S

swiftcode

Hi all,

I seem to have a problem with making my dates auto adjust itself. Here's
what the problem is. I would like to have a date whereby if it is falls on a
weekend to auto adjust itself to monday, but if i have a holiday adjustment,
then to take into account the number of holidays and adjust accordingly. This
is what i've doe so fat bu it doesn't seem to work.

Function SetDate(Current_Date, Holiday_Adjustment)

If Holiday_Adjustment = "T" Then
Date_Adj = 0
ElseIf Holiday_Adjustment = "T + 1" Then
Date_Adj = 1
ElseIf Holiday_Adjustment = "T + 2" Then
Date_Adj = 2
ElseIf Holiday_Adjustment = "T + 3" Then
Date_Adj = 3
End If

WeekDayNum = Weekday(Current_Date)

If WeekDayNum = 2 Then
Date_Adj1 = 0
ElseIf WeekDayNum = 3 Then
Date_Adj1 = 0
ElseIf WeekDayNum = 4 Then
Date_Adj1 = 0
ElseIf WeekDayNum = 5 Then
Date_Adj1 = 0
ElseIf WeekDayNum = 6 Then
Date_Adj1 = 0
ElseIf WeekDayNum = 7 Then
Date_Adj1 = 2
ElseIf WeekDayNum = 1 Then
Date_Adj1 = 1
End If

SetDate = Current_Date + Date_Adj + Date_Adj1

End Function

I would appreciate any help that anyone can give. Thank you in advance.

Rgds
Ray
 
J

Jacob Skaria

Try the below

Function SetDate(Current_Date, Holiday_Adjustment)
SetDate = Current_Date + Val(Replace(Holiday_Adjustment, "T", ""))
If Weekday(SetDate) = 1 Then SetDate = SetDate + 1
If Weekday(SetDate) = 7 Then SetDate = SetDate + 2
End Function

If this post helps click Yes
 
S

swiftcode

Hi Jacob,

Thanks, it seems to work on the normal date, but the holiday adjsutment if
it falls on a weekend will still reflect the weekend date.

Wuld it be possible for example, if i have a holiday adjustment for "T" and
my current date falls on a saturday to take automatically monday, or if "T +
1" also monday, but "T + 2" and "T + 3" to give tuesday and wednesday
respectively?

Many thanks for your help on this.

Rgds
Ray
 
J

Jacob Skaria

OK. Can you explain "Holiday_Adjustment".

Whatif Friday and T+2
Whatif Thursday and T+3
Whatif Sunday and T+1

If this post helps click Yes
 
J

john

Not tested but see if this does what you want:

Function SetDate(Current_Date, Holiday_Adjustment)

WeekDayNum = Weekday(Current_Date)

If WeekDayNum = 7 Or WeekDayNum = 1 Then

Do Until Weekday(Current_Date) = 2

Current_Date = Current_Date + 1

Loop

End If

If Holiday_Adjustment = "T" Then

Date_Adj = 0

ElseIf Holiday_Adjustment = "T + 1" Then

Date_Adj = 1

ElseIf Holiday_Adjustment = "T + 2" Then

Date_Adj = 2

ElseIf Holiday_Adjustment = "T + 3" Then

Date_Adj = 3

End If

SetDate = Current_Date + Date_Adj

End Function
 
S

swiftcode

Hi John,

It doesn't seem to be giving the expected results. Thanks for your help.

Rgds
Ray
 
S

swiftcode

Hi Jacob,

basically it is just simply Today (T) + # (0,1,2,3)

i wanted to add a holiday input so that incase eg. Friday is a holiday, i
can make the date skip to Monday, or to the next working day.

Thanks for your help.

Rgds
Ray
 
S

swiftcode

Hi Jacob,

Sorry if i have been unclear, but just to add to my reply and clarify your
understanding of my question:

Whatif Friday and T+2 = Tuesday
Whatif Thursday and T+3 = Tuesday
Whatif Sunday and T+1 = Monday


Many thanks for your help.

Rgds
Raymond
 
J

Jacob Skaria

Try the below..

Function SetDate(Current_Date, Holiday_Adjustment)
SetDate = Current_Date + Val(Replace(Holiday_Adjustment, "T", "")) + 1
If Weekday(SetDate) = 1 Then SetDate = SetDate + 1
If Weekday(SetDate) = 7 Then SetDate = SetDate + 2
End Function


If this post helps click Yes
 
R

Ron Rosenfeld

Hi all,

I seem to have a problem with making my dates auto adjust itself. Here's
what the problem is. I would like to have a date whereby if it is falls on a
weekend to auto adjust itself to monday, but if i have a holiday adjustment,
then to take into account the number of holidays and adjust accordingly. This
is what i've doe so fat bu it doesn't seem to work.

Function SetDate(Current_Date, Holiday_Adjustment)

If Holiday_Adjustment = "T" Then
Date_Adj = 0
ElseIf Holiday_Adjustment = "T + 1" Then
Date_Adj = 1
ElseIf Holiday_Adjustment = "T + 2" Then
Date_Adj = 2
ElseIf Holiday_Adjustment = "T + 3" Then
Date_Adj = 3
End If

WeekDayNum = Weekday(Current_Date)

If WeekDayNum = 2 Then
Date_Adj1 = 0
ElseIf WeekDayNum = 3 Then
Date_Adj1 = 0
ElseIf WeekDayNum = 4 Then
Date_Adj1 = 0
ElseIf WeekDayNum = 5 Then
Date_Adj1 = 0
ElseIf WeekDayNum = 6 Then
Date_Adj1 = 0
ElseIf WeekDayNum = 7 Then
Date_Adj1 = 2
ElseIf WeekDayNum = 1 Then
Date_Adj1 = 1
End If

SetDate = Current_Date + Date_Adj + Date_Adj1

End Function

I would appreciate any help that anyone can give. Thank you in advance.

Rgds
Ray
Click to expand...

Why not just use the WORKDAY function? If you have a version of Excel prior to
2007, you will need to install the analysis toolpak.

Then you could have a list of holidays someplace, and merely input that range
or array as an argument.

For example, with a list of holidays in a range named "Holidays", you could use
any of the following:

With date to be "adjusted" in A1:
=WORKDAY(A1-1,1,Holidays)

VBA variant for Excel 2007:

Function SetDate(Current_Date As Date, Holidays As Range) As Date
SetDate = WorksheetFunction.WorkDay(Current_Date - 1, 1, Holidays)
End Function

For versions of Excel prior to 2007, I believe you have to set a reference to
atpvbaen.xls (under the main menu for VBA, see Tools/References), and then you
can use the command directly.

If, for some reason, you don't want to use the builtin WORKDAY function, you
could use this:

==========================
Option Explicit
Function SetDate(Current_Date As Date, Holidays As Range) As Date
Dim i As Long
Dim TempDate As Date
Dim c As Range
Dim Stp As Integer
Const NumDays As Long = 1

Stp = Sgn(NumDays)
TempDate = Current_Date - 1
For i = Stp To NumDays Step Stp
TempDate = TempDate + Stp
If Weekday(TempDate) = vbSaturday Then _
TempDate = TempDate + Stp - (Stp > 0)
If Weekday(TempDate) = vbSunday Then _
TempDate = TempDate + Stp + (Stp < 0)

If Not Holidays Is Nothing Then
Do Until Not IsError(Application.Match(CDbl(TempDate), Holidays, 0)) = False
If IsError(Application.Match(CDbl(TempDate), Holidays, 0)) = False Then
TempDate = TempDate + Stp
If Weekday(TempDate) = vbSaturday Then _
TempDate = TempDate + Stp - (Stp > 0)
If Weekday(TempDate) = vbSunday Then _
TempDate = TempDate + Stp + (Stp < 0)
End If
Loop
End If
Next i
SetDate = TempDate
End Function
=====================================
--ron
 
S

swiftcode

Hi all,

Thank you very much for your help on this. Using a combination of your ideas
and solutions, i have found a solution to my problem. I know it may not be
the most efficient, however, this seems to work, it would be great if anyone
could help me to shorten and make the coding more efficient.

----------------------------------------------------------------------------
Function SetDate(Current_Date, Holiday_Adjustment)

Dim SDate As Date

HAdj = Val(Replace(Holiday_Adjustment, "T", ""))
SDate = Current_Date

If (HAdj = "" Or HAdj = 0) And Weekday(SDate) = 1 Then
SDate = SDate + 1
ElseIf (HAdj = "" Or HAdj = 0) And Weekday(SDate) = 7 Then
SDate = SDate + 2
ElseIf HAdj = 1 And Weekday(SDate) = 2 Then
SDate = SDate + 1
ElseIf HAdj = 1 And Weekday(SDate) = 3 Then
SDate = SDate + 1
ElseIf HAdj = 1 And Weekday(SDate) = 4 Then
SDate = SDate + 1
ElseIf HAdj = 1 And Weekday(SDate) = 5 Then
SDate = SDate + 1
ElseIf HAdj = 1 And Weekday(SDate) = 6 Then
SDate = SDate + 3
ElseIf HAdj = 1 And Weekday(SDate) = 7 Then
SDate = SDate + 2
ElseIf HAdj = 1 And Weekday(SDate) = 1 Then
SDate = SDate + 1

ElseIf HAdj = 2 And Weekday(SDate) = 2 Then
SDate = SDate + 2
ElseIf HAdj = 2 And Weekday(SDate) = 3 Then
SDate = SDate + 2
ElseIf HAdj = 2 And Weekday(SDate) = 4 Then
SDate = SDate + 2
ElseIf HAdj = 2 And Weekday(SDate) = 5 Then
SDate = SDate + 4
ElseIf HAdj = 2 And Weekday(SDate) = 6 Then
SDate = SDate + 4
ElseIf HAdj = 2 And Weekday(SDate) = 7 Then
SDate = SDate + 3
ElseIf HAdj = 2 And Weekday(SDate) = 1 Then
SDate = SDate + 2

ElseIf HAdj = 3 And Weekday(SDate) = 2 Then
SDate = SDate + 3
ElseIf HAdj = 3 And Weekday(SDate) = 3 Then
SDate = SDate + 3
ElseIf HAdj = 3 And Weekday(SDate) = 4 Then
SDate = SDate + 5
ElseIf HAdj = 3 And Weekday(SDate) = 5 Then
SDate = SDate + 5
ElseIf HAdj = 3 And Weekday(SDate) = 6 Then
SDate = SDate + 5
ElseIf HAdj = 3 And Weekday(SDate) = 7 Then
SDate = SDate + 4
ElseIf HAdj = 3 And Weekday(SDate) = 1 Then
SDate = SDate + 3
End If

SetDate = SDate

End Function
---------------------------------------------------------------------------

Many thanks to everyone for all the help given here.

Rgds
Ray
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Why am I getting run-time error '3256' when Excel workbook is shar 3
Report Freeze Due to On_Format Event 1
On Format Print Preview Freeze 1
Excel2000: Need help on UDF (working with arrays) 2
Extremely Slow Code 3
Days between 2 dates excluding Sundays only 1
Track Changes Issues 1
Count the remaining weekdays in a data range excluding holidays and start/end dates 5

Top