| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date input to exclude weekends
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date input to exclude weekends
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date input to exclude weekends
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date input to exclude weekends
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date input to exclude weekends
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date input to exclude weekends
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date input to exclude weekends
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date input to exclude weekends
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date input to exclude weekends
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date input to exclude weekends
On Sun, 11 Oct 2009 20:54:01 -0700, 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 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date input to exclude weekends
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 "Ron Rosenfeld" wrote: On Sun, 11 Oct 2009 20:54:01 -0700, 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 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Generate Payment Due Date to exclude Weekends and Public Holiday | Excel Worksheet Functions | |||
| Exclude Weekends | Excel Worksheet Functions | |||
| Date count to exclude weekends | Excel Worksheet Functions | |||
| Exclude Weekends | Charts and Charting in Excel | |||
| Date Calculation to exclude weekends | Excel Worksheet Functions | |||
Linear Mode
