PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Date input to exclude weekends

Reply
Page 1 of 2 1 2
Thread Tools Rate Thread

Date input to exclude weekends

 
 
swiftcode
Guest
Posts: n/a
 
      12th Oct 2009
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
 
Reply With Quote
 
 
 
 
Jacob Skaria
Guest
Posts: n/a
 
      12th Oct 2009
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
---------------
Jacob Skaria


"swiftcode" wrote:

> 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
 
Reply With Quote
 
swiftcode
Guest
Posts: n/a
 
      12th Oct 2009
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

"Jacob Skaria" wrote:

> 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
> ---------------
> Jacob Skaria
>
>
> "swiftcode" wrote:
>
> > 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
 
Reply With Quote
 
Jacob Skaria
Guest
Posts: n/a
 
      12th Oct 2009
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
---------------
Jacob Skaria


"swiftcode" wrote:

> 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
>
> "Jacob Skaria" wrote:
>
> > 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
> > ---------------
> > Jacob Skaria
> >
> >
> > "swiftcode" wrote:
> >
> > > 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
 
Reply With Quote
 
john
Guest
Posts: n/a
 
      12th Oct 2009
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
--
jb


"swiftcode" wrote:

> 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
 
Reply With Quote
 
swiftcode
Guest
Posts: n/a
 
      12th Oct 2009
Hi John,

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

Rgds
Ray

"john" wrote:

> 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
> --
> jb
>
>
> "swiftcode" wrote:
>
> > 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
 
Reply With Quote
 
swiftcode
Guest
Posts: n/a
 
      12th Oct 2009
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

"Jacob Skaria" wrote:

> 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
> ---------------
> Jacob Skaria
>
>
> "swiftcode" wrote:
>
> > 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
> >
> > "Jacob Skaria" wrote:
> >
> > > 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
> > > ---------------
> > > Jacob Skaria
> > >
> > >
> > > "swiftcode" wrote:
> > >
> > > > 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
 
Reply With Quote
 
swiftcode
Guest
Posts: n/a
 
      12th Oct 2009
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

"swiftcode" wrote:

> 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
>
> "Jacob Skaria" wrote:
>
> > 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
> > ---------------
> > Jacob Skaria
> >
> >
> > "swiftcode" wrote:
> >
> > > 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
> > >
> > > "Jacob Skaria" wrote:
> > >
> > > > 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
> > > > ---------------
> > > > Jacob Skaria
> > > >
> > > >
> > > > "swiftcode" wrote:
> > > >
> > > > > 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
 
Reply With Quote
 
Jacob Skaria
Guest
Posts: n/a
 
      12th Oct 2009
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
---------------
Jacob Skaria


"swiftcode" wrote:

> 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
>
> "Jacob Skaria" wrote:
>
> > 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
> > ---------------
> > Jacob Skaria
> >
> >
> > "swiftcode" wrote:
> >
> > > 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
> > >
> > > "Jacob Skaria" wrote:
> > >
> > > > 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
> > > > ---------------
> > > > Jacob Skaria
> > > >
> > > >
> > > > "swiftcode" wrote:
> > > >
> > > > > 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
 
Reply With Quote
 
Ron Rosenfeld
Guest
Posts: n/a
 
      12th Oct 2009
On Sun, 11 Oct 2009 20:54:01 -0700, swiftcode
<(E-Mail Removed)> wrote:

>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

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
 
Reply With Quote
 
 
 
Reply
Page 1 of 2 1 2

« Add in specific cells | RE: Apology »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Date()-2 (exclude weekends) dchristo Microsoft Access 4 12th Aug 2008 08:23 PM
Date expressions that can exclude weekends =?Utf-8?B?TWljaGVsbGU=?= Microsoft Access Queries 1 14th Feb 2007 04:29 PM
Date Calculation to exclude weekends =?Utf-8?B?Vmlt?= Microsoft Excel Worksheet Functions 2 24th Jan 2006 02:58 PM
Date Difference Exclude Weekends =?Utf-8?B?TmFkU2FkQmFk?= Microsoft Access Queries 2 13th Jun 2005 03:37 PM
Exclude weekends from date calculation =?Utf-8?B?Q01E?= Microsoft Access 5 11th Jan 2005 03:45 AM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 01:20 PM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top