With monday.comâ€™s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Hi Experts,

In the code below, the Public Function Work_Days works perfectly and calculates the number of days between two dates excluding weekends and holidays.

I am trying to write a function which essentially does this:

I give the function an end date and tell the function to subtract 10 work days to give me a start date. My attempt is the code below (Public Function Work_Days_Subtract()). It is getting stuck on 'Loop' with a compile error of 'Loop without Do'. This is my first attempt at writing this type of loop code by myself, but I thought the 'Do Until' would couple with the loop...any pointers would be appreciated.

Thanks!

Option Compare Database

Option Explicit

Public Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

' "Calculating the workdays between Dates"

' Note that this function accounts for holidays.

Dim WholeWeeks As Variant

Dim DateCnt As Variant

Dim EndDays As Integer

BegDate = DateValue(BegDate)

EndDate = DateValue(EndDate)

DateCnt = BegDate

EndDays = 0

Do While DateCnt <= EndDate

If Not IsNull(DLookup("Holiday", "t_Holiday_Dates", "[Holiday]=#" & DateCnt & "#")) Then

EndDays = EndDays - 1

End If

If Format(DateCnt, "ddd") <> "Sun" And _

Format(DateCnt, "ddd") <> "Sat" Then

EndDays = EndDays + 1

End If

DateCnt = DateAdd("d", 1, DateCnt)

Loop

Work_Days = EndDays

End Function

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)

Loop

End If

End Function

In the code below, the Public Function Work_Days works perfectly and calculates the number of days between two dates excluding weekends and holidays.

I am trying to write a function which essentially does this:

I give the function an end date and tell the function to subtract 10 work days to give me a start date. My attempt is the code below (Public Function Work_Days_Subtract()). It is getting stuck on 'Loop' with a compile error of 'Loop without Do'. This is my first attempt at writing this type of loop code by myself, but I thought the 'Do Until' would couple with the loop...any pointers would be appreciated.

Thanks!

Option Compare Database

Option Explicit

Public Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

' "Calculating the workdays between Dates"

' Note that this function accounts for holidays.

Dim WholeWeeks As Variant

Dim DateCnt As Variant

Dim EndDays As Integer

BegDate = DateValue(BegDate)

EndDate = DateValue(EndDate)

DateCnt = BegDate

EndDays = 0

Do While DateCnt <= EndDate

If Not IsNull(DLookup("Holiday", "t_Holiday_Dates", "[Holiday]=#" & DateCnt & "#")) Then

EndDays = EndDays - 1

End If

If Format(DateCnt, "ddd") <> "Sun" And _

Format(DateCnt, "ddd") <> "Sat" Then

EndDays = EndDays + 1

End If

DateCnt = DateAdd("d", 1, DateCnt)

Loop

Work_Days = EndDays

End Function

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)

Loop

End If

End Function

Regards,

Sean

```
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
```

I made the above correction, however, when I try to use the function, the database gets stuck and I have to force close the database ...I have attached a sample database. I am trying to use the function in the query called q_3_Daily_Count. The module referenced in this question is under: Work_Days - Counts business days

Expert-Exchange-Version.zip

```
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 happensyour loop will never quit. CTRL-BREAK (Upper right on keyboard) will break the code, so you can end it.

There's a bunch of ways to build safety in.

How many times should the code EVER loop?

Put in an incrementer (X) and bump it up once, each loop and bail if it gets too big:

```
Dim 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
```

Now the code will not run away infinitely

Can you post your actual code here?

```
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
```

-Bear

```
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
```

All Courses

From novice to tech pro — start learning today.

You would call it like this:

Open in new window

I have put the code into your sample database and it is working.

TIP: Compact your database before posting. It went from 180+ meg to 3+ meg.

See attached:

Expert-Exchange-Version-HiTechCo.zip