Public Function Work_Days_Subtract() As Date
Dim INumDaysToSubtract As Integer
Dim INumCalendarDays As Integer
Dim StartDate As Date
Dim EndDate As Date
INumDaysToSubtract = 10
INumCalendarDays = -20
EndDate = DLookup("MaxOfDataDate", "q_DataDate_MAX")
StartDate = DateAdd("d", INumCalendarDays, EndDate)
Do Until Work_Days_Subtract = INumDaysToSubtract
If Work_Days(StartDate, EndDate) = INumDaysToSubtract Then
Work_Days_Subtract = StartDate
Exit Do
Else
INumCalendarDays = INumCalendarDays - 1
StartDate = DateAdd("d", INumCalendarDays, EndDate)
End If
Loop
End Function
Do Until Work_Days_Subtract = INumDaysToSubtract
If Work_Days(StartDate, EndDate) = INumDaysToSubtract Then
Work_Days_Subtract = StartDate
Exit Do
Else
INumCalendarDays = INumCalendarDays - 1
StartDate = DateAdd("d", INumCalendarDays, EndDate)
End If
Loop
If this never happensDim x as integer
x = 0
Do Until Work_Days_Subtract = INumDaysToSubtract or X > 1000 'or whatever
If Work_Days(StartDate, EndDate) = INumDaysToSubtract Then
Work_Days_Subtract = StartDate
Exit Do
Else
INumCalendarDays = INumCalendarDays - 1
StartDate = DateAdd("d", INumCalendarDays, EndDate)
End If
x = x + 1
Loop
Public Function Work_Days_Subtract() As Date
Dim INumDaysToSubtract As Integer
Dim intNonWeekendDays As Integer
Dim StartDate As Date
Dim EndDate As Date
INumDaysToSubtract = 10
' Find End Date to use
EndDate = DLookup("MaxOfDataDate", "q_DataDate_MAX")
' Calculate our Start Date
' First determine how far back to go by dividing the Number of Days by 7 -- INumDaysToSubtract / 7
' Use the Int function to drop any remainder--Int(INumDaysToSubtract / 7)
' Now multiple by 2 for Sat & Sun--(Int(INumDaysToSubtract / 7) * 2
' This is the number of weekend days in the number of days to subtract
' Now add back to the original number for the number of business days minues holidays or other potential weekend days
' subtract one to not include the start Date itself.
' This just gives a good starting point. Now we just need to work back to find any other
' weekend day or holidays
intNonWeekendDays = INumDaysToSubtract + (Int(INumDaysToSubtract / 7) * 2) - 1
StartDate = DateAdd("d", -intNonWeekendDays, EndDate)
Do Until Work_Days(StartDate, EndDate) = INumDaysToSubtract
' Move back one more day
StartDate = DateAdd("d", -1, StartDate)
Loop
Work_Days_Subtract = StartDate
End Function
=fAddWorkdays(DLookup("MaxOfDataDate", "q_DataDate_MAX"), -10)
CREATE TABLE [WorkDays] (
[date] [datetime] NOT NULL PRIMARY KEY,
[workday] [bit] NOT NULL DEFAULT (1),
[workday_index] [int] NULL
GO
-- fill the above table programmatically, then manually specify the holidays
CREATE function WorkDateAdd(@Date as datetime, @Num as integer) returns datetime begin
declare @d datetime
select @d=w2.[date]
from workdays w2 join workdays w1
on w2.workday=1
and w2.workday_index-w1.workday_index=@num
where w1.[date]=cast(floor(cast(@date as float) ) as datetime)
return @d
end
GO
/*
The column workday_index is there to speed up the calculations. It has to be
re-populated whenever the workdays are changed (such as when the company
determines the holiday dates for the next year), by the following script:
*/
update workdays set workday_index=null
declare c cursor local fast_forward for
select [date],workday from workdays order by 1
declare @i int, @d datetime, @w bit
select @i=0
open c
fetch c into @d,@w
while @@fetch_status=0 begin
if @w=1 set @i=@i+1
update workdays set workday_index=@i where [date]=@d
fetch c into @d,@w
end
close c
deallocate c
Public Function WorkDaysPrevious(Previous as Integer, StartDate as Date) As Date
'ok, we have Public Function Work_Days, which will calculate how many workdays between two given dates and it works
'so let's not re-invent the wheel
'Previous is the interval we want
'so we want an iteration that will call Work_Days until Work_Days = Previous
'now, worst case scenario is that each work week is only 3 days long
Dim StartPoint as Integer
Dim BegDate as Date
Dim Done as boolean
'Break Previous into 3 day 'weeks'
StartPoint = CInt(Previous/3) +1 'round it off, but bump it by one just in case
StartPoint = StartPoint * 7 'cause there are 7 days per week!
'StartPoint is far enough in the past that walking forward we'll get our answer
'Maybe too far for optimum performance
'Adjust as you think needful!
'initialize variables
Done = False
BegDate = DateAdd("d", -1 * StartPoint, StartDate)
'BegDate is before start date
'Let's say Previous was 15
'StartPoint would be ((15/3)+1)*7
'Or 42 days
'For sure, as we walk forward from 42 calendar days before our date _
'we'll hit 15 workdays before our date in the Previous = 15 scenario!
Do Until Done = True or BegDate = StartDate ' get an answer or bail when the loop is no longer logical!
Done = (Work_Days(BegDate, StartDate) = Previous)
if Done = False then
'step forward a day and try again
BegDate = DateAdd("d", 1, BegDate)
End If
Loop
WorkDaysPrevious = BegDate
End Function
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
Export Access Query To Excell | 16 | 43 | |
Run-time error '1004' after second execution of code. | 4 | 37 | |
iPhone excel activation issues | 11 | 65 | |
Newbie needs help printing from a form. | 10 | 19 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
9 Experts available now in Live!