Weekdays Utilization Challenge

[DubboPete]

Hi all,

In my table there are many bookings entries for a vehicle, covering a
calendar month or longer. What I am trying to do is calculate the
number of weekdays any given vehicle is not booked within a specified
month. Here's some examples.

Vehicle ABC123 has the following bookings ([startdate] [enddate])

01-Sep-09 03-Sep-09
11-Sep-09 18-Sep-09

In the perfect world, I'd like to update the field [nonbooked] with
the value 15.

1st to the 3rd is 3 working days. 11th to 18th is 6 working days,
and two weekend days. 22 working days in September-09 minus the sum(3
+ 6) = 15.

I'd like to be able to run this on my table for every entry, but ONLY
for a specified month period. That's because there are going to be
bookings that started in August 09, or may end in October 09, but I'm
only interested in what happens for the specified period.

So, if the bookings were ([startdate] [enddate])

31-Aug-09 03-Sep-09
11-Sep-09 18-Sep-09

the result would be the same 15, as 31-Aug-09 is ignored, but the 3
days in September for that booking are not!

Anyone up for the challenge? And, can this run from a command button
on a form please??

TIA

Pete

 

[Tom van Stiphout]

On Wed, 7 Oct 2009 17:54:34 -0700 (PDT), DubboPete

This problem is known as the "gaps and islands" problem. It is covered
in Chapter 5 of an upcoming book written by several SQL Server MVPs
which I collaborated on as well. Here is a link to this highly
recommended work:
http://www.manning.com/nielsen/

-Tom.
Microsoft Access MVP

 

[Klatuu]

You should not update a field with this information. You should calculate it
only when you need it for a report or a form. It violates database
normalization rules and has a good chance of getting out of sync and being
incorrect.

Here is how I would go about it.
First, here is a function that returns the number of working days between
two dates:

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays tabl
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHoliday", _
"[holidate] between " & Format(dtmStart, "\#yyyy\-mm\-dd\#") & _
" And " & Format(dtmEnd, "\#yyyy\-mm\-dd\#"))

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

------
Note the reference to the Holiday table. That is a table I use to be able
to know what weekdays should not be counted. Don't put any Saturday or
Sunday holidays it the table or it will throw the count off. If you don't
want to consider holidays, just remove this part:

'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHoliday", _
"[holidate] between " & Format(dtmStart, "\#yyyy\-mm\-dd\#") & _
" And " & Format(dtmEnd, "\#yyyy\-mm\-dd\#"))

Here is a sample query that will return the number of days booked and the
number of days not booked for each vehicle:

SELECT tblClient.MainName, Count(tblClient.MainName) AS NameCount,
CalcWorkDays(#10/1/2009#,#10/31/2009#)-[NameCount] AS DaysNotBooked
FROM tblClient
WHERE (((tblClient.EnteredOn) Between #1/1/1980# And #12/31/2010#))
GROUP BY tblClient.MainName;

You will need to change all the names. I threw this together with a table I
use for testing. Of course you can use parameters or reference an open form
control to establish the date range for the Between operator.


--
Dave Hargis, Microsoft Access MVP

Hi all,

In my table there are many bookings entries for a vehicle, covering a
calendar month or longer. What I am trying to do is calculate the
number of weekdays any given vehicle is not booked within a specified
month. Here's some examples.

Vehicle ABC123 has the following bookings ([startdate] [enddate])

01-Sep-09 03-Sep-09
11-Sep-09 18-Sep-09

In the perfect world, I'd like to update the field [nonbooked] with
the value 15.

1st to the 3rd is 3 working days. 11th to 18th is 6 working days,
and two weekend days. 22 working days in September-09 minus the sum(3
+ 6) = 15.

I'd like to be able to run this on my table for every entry, but ONLY
for a specified month period. That's because there are going to be
bookings that started in August 09, or may end in October 09, but I'm
only interested in what happens for the specified period.

So, if the bookings were ([startdate] [enddate])

31-Aug-09 03-Sep-09
11-Sep-09 18-Sep-09

the result would be the same 15, as 31-Aug-09 is ignored, but the 3
days in September for that booking are not!

Anyone up for the challenge? And, can this run from a command button
on a form please??

TIA

Pete

 

[Clifford Bass]

Hi David,

That is a different variation on calculating work days. I was curious
as to how it worked and so played around with it. You may want to check it's
accuracy. When the start day is a Saturday or a Sunday I get erroneous
results:

Start_Date End_Date Workdays
Saturday, October 03, 2009 Saturday, October 03, 2009 1
Saturday, October 03, 2009 Sunday, October 04, 2009 1
Saturday, October 03, 2009 Monday, October 05, 2009 2
Saturday, October 03, 2009 Tuesday, October 06, 2009 3
Saturday, October 03, 2009 Wednesday, October 07, 2009 4
Saturday, October 03, 2009 Thursday, October 08, 2009 5
Saturday, October 03, 2009 Friday, October 09, 2009 6
Saturday, October 03, 2009 Saturday, October 10, 2009 6
Saturday, October 03, 2009 Sunday, October 11, 2009 6


Start_Date End_Date Workdays
Sunday, October 04, 2009 Sunday, October 04, 2009 1
Sunday, October 04, 2009 Monday, October 05, 2009 2
Sunday, October 04, 2009 Tuesday, October 06, 2009 3
Sunday, October 04, 2009 Wednesday, October 07, 2009 4
Sunday, October 04, 2009 Thursday, October 08, 2009 5
Sunday, October 04, 2009 Friday, October 09, 2009 6
Sunday, October 04, 2009 Saturday, October 10, 2009 6
Sunday, October 04, 2009 Sunday, October 11, 2009 6

Clifford Bass

You should not update a field with this information. You should calculate it
only when you need it for a report or a form. It violates database
normalization rules and has a good chance of getting out of sync and being
incorrect.

Here is how I would go about it.
First, here is a function that returns the number of working days between
two dates:

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHoliday", _
"[holidate] between " & Format(dtmStart, "\#yyyy\-mm\-dd\#") & _
" And " & Format(dtmEnd, "\#yyyy\-mm\-dd\#"))

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

------
Note the reference to the Holiday table. That is a table I use to be able
to know what weekdays should not be counted. Don't put any Saturday or
Sunday holidays it the table or it will throw the count off. If you don't
want to consider holidays, just remove this part:

'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHoliday", _
"[holidate] between " & Format(dtmStart, "\#yyyy\-mm\-dd\#") & _
" And " & Format(dtmEnd, "\#yyyy\-mm\-dd\#"))

Here is a sample query that will return the number of days booked and the
number of days not booked for each vehicle:

SELECT tblClient.MainName, Count(tblClient.MainName) AS NameCount,
CalcWorkDays(#10/1/2009#,#10/31/2009#)-[NameCount] AS DaysNotBooked
FROM tblClient
WHERE (((tblClient.EnteredOn) Between #1/1/1980# And #12/31/2010#))
GROUP BY tblClient.MainName;

You will need to change all the names. I threw this together with a table I
use for testing. Of course you can use parameters or reference an open form
control to establish the date range for the Between operator.

 

[Klatuu]

Simple solution --- Don't start on a weekend

Thanks, I have never noticed that before, I will work on it.
Glad to know you were interested enough to play around with it.
If you see a solution before I get to it, let me know.
--
Dave Hargis, Microsoft Access MVP

Hi David,

That is a different variation on calculating work days. I was curious
as to how it worked and so played around with it. You may want to check it's
accuracy. When the start day is a Saturday or a Sunday I get erroneous
results:

Start_Date End_Date Workdays
Saturday, October 03, 2009 Saturday, October 03, 2009 1
Saturday, October 03, 2009 Sunday, October 04, 2009 1
Saturday, October 03, 2009 Monday, October 05, 2009 2
Saturday, October 03, 2009 Tuesday, October 06, 2009 3
Saturday, October 03, 2009 Wednesday, October 07, 2009 4
Saturday, October 03, 2009 Thursday, October 08, 2009 5
Saturday, October 03, 2009 Friday, October 09, 2009 6
Saturday, October 03, 2009 Saturday, October 10, 2009 6
Saturday, October 03, 2009 Sunday, October 11, 2009 6


Start_Date End_Date Workdays
Sunday, October 04, 2009 Sunday, October 04, 2009 1
Sunday, October 04, 2009 Monday, October 05, 2009 2
Sunday, October 04, 2009 Tuesday, October 06, 2009 3
Sunday, October 04, 2009 Wednesday, October 07, 2009 4
Sunday, October 04, 2009 Thursday, October 08, 2009 5
Sunday, October 04, 2009 Friday, October 09, 2009 6
Sunday, October 04, 2009 Saturday, October 10, 2009 6
Sunday, October 04, 2009 Sunday, October 11, 2009 6

Clifford Bass

You should not update a field with this information. You should calculate it
only when you need it for a report or a form. It violates database
normalization rules and has a good chance of getting out of sync and being
incorrect.

Here is how I would go about it.
First, here is a function that returns the number of working days between
two dates:

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHoliday", _
"[holidate] between " & Format(dtmStart, "\#yyyy\-mm\-dd\#") & _
" And " & Format(dtmEnd, "\#yyyy\-mm\-dd\#"))

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

------
Note the reference to the Holiday table. That is a table I use to be able
to know what weekdays should not be counted. Don't put any Saturday or
Sunday holidays it the table or it will throw the count off. If you don't
want to consider holidays, just remove this part:

'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHoliday", _
"[holidate] between " & Format(dtmStart, "\#yyyy\-mm\-dd\#") & _
" And " & Format(dtmEnd, "\#yyyy\-mm\-dd\#"))

Here is a sample query that will return the number of days booked and the
number of days not booked for each vehicle:

SELECT tblClient.MainName, Count(tblClient.MainName) AS NameCount,
CalcWorkDays(#10/1/2009#,#10/31/2009#)-[NameCount] AS DaysNotBooked
FROM tblClient
WHERE (((tblClient.EnteredOn) Between #1/1/1980# And #12/31/2010#))
GROUP BY tblClient.MainName;

You will need to change all the names. I threw this together with a table I
use for testing. Of course you can use parameters or reference an open form
control to establish the date range for the Between operator.

 

[Clifford Bass]

Hi David,

Simple is good! Unfortunately, things intrude!

I will post if I see a solution. It actually has prompted me to relook
at my business-days function and see what ways it can be improved.

Clifford Bass

 

[Clifford Bass]

Hi David,

If you add this to the top of your function, it takes care of the
problem:

---------------------------------

Dim intWeekday As Integer

intWeekday = Weekday(dtmStart, vbSaturday)
If intWeekday <= 2 Then
' Move start date off of the weekend
dtmStart = dtmStart + 3 - intWeekday
End If

---------------------------------

Clifford Bass

Simple solution --- Don't start on a weekend

Thanks, I have never noticed that before, I will work on it.
Glad to know you were interested enough to play around with it.
If you see a solution before I get to it, let me know.
--
Dave Hargis, Microsoft Access MVP

Hi David,

That is a different variation on calculating work days. I was curious
as to how it worked and so played around with it. You may want to check it's
accuracy. When the start day is a Saturday or a Sunday I get erroneous
results:

Start_Date End_Date Workdays
Saturday, October 03, 2009 Saturday, October 03, 2009 1
Saturday, October 03, 2009 Sunday, October 04, 2009 1
Saturday, October 03, 2009 Monday, October 05, 2009 2
Saturday, October 03, 2009 Tuesday, October 06, 2009 3
Saturday, October 03, 2009 Wednesday, October 07, 2009 4
Saturday, October 03, 2009 Thursday, October 08, 2009 5
Saturday, October 03, 2009 Friday, October 09, 2009 6
Saturday, October 03, 2009 Saturday, October 10, 2009 6
Saturday, October 03, 2009 Sunday, October 11, 2009 6


Start_Date End_Date Workdays
Sunday, October 04, 2009 Sunday, October 04, 2009 1
Sunday, October 04, 2009 Monday, October 05, 2009 2
Sunday, October 04, 2009 Tuesday, October 06, 2009 3
Sunday, October 04, 2009 Wednesday, October 07, 2009 4
Sunday, October 04, 2009 Thursday, October 08, 2009 5
Sunday, October 04, 2009 Friday, October 09, 2009 6
Sunday, October 04, 2009 Saturday, October 10, 2009 6
Sunday, October 04, 2009 Sunday, October 11, 2009 6

Clifford Bass

You should not update a field with this information. You should calculate it
only when you need it for a report or a form. It violates database
normalization rules and has a good chance of getting out of sync and being
incorrect.

Here is how I would go about it.
First, here is a function that returns the number of working days between
two dates:

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHoliday", _
"[holidate] between " & Format(dtmStart, "\#yyyy\-mm\-dd\#") & _
" And " & Format(dtmEnd, "\#yyyy\-mm\-dd\#"))

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

------
Note the reference to the Holiday table. That is a table I use to be able
to know what weekdays should not be counted. Don't put any Saturday or
Sunday holidays it the table or it will throw the count off. If you don't
want to consider holidays, just remove this part:

'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHoliday", _
"[holidate] between " & Format(dtmStart, "\#yyyy\-mm\-dd\#") & _
" And " & Format(dtmEnd, "\#yyyy\-mm\-dd\#"))

Here is a sample query that will return the number of days booked and the
number of days not booked for each vehicle:

SELECT tblClient.MainName, Count(tblClient.MainName) AS NameCount,
CalcWorkDays(#10/1/2009#,#10/31/2009#)-[NameCount] AS DaysNotBooked
FROM tblClient
WHERE (((tblClient.EnteredOn) Between #1/1/1980# And #12/31/2010#))
GROUP BY tblClient.MainName;

You will need to change all the names. I threw this together with a table I
use for testing. Of course you can use parameters or reference an open form
control to establish the date range for the Between operator.

 

[Clifford Bass]

Hi David,

If you add it, make sure to change your function declaration to:

Function CalcWorkDays(ByVal dtmStart As Date, ByVal dtmEnd As Date) As Integer

Actually, it would be good to do that anyway.

Clifford Bass

Hi David,

If you add this to the top of your function, it takes care of the
problem:

---------------------------------

Dim intWeekday As Integer

intWeekday = Weekday(dtmStart, vbSaturday)
If intWeekday <= 2 Then
' Move start date off of the weekend
dtmStart = dtmStart + 3 - intWeekday
End If

---------------------------------

Clifford Bass

Simple solution --- Don't start on a weekend

Thanks, I have never noticed that before, I will work on it.
Glad to know you were interested enough to play around with it.
If you see a solution before I get to it, let me know.
--
Dave Hargis, Microsoft Access MVP

Hi David,

That is a different variation on calculating work days. I was curious
as to how it worked and so played around with it. You may want to check it's
accuracy. When the start day is a Saturday or a Sunday I get erroneous
results:

Start_Date End_Date Workdays
Saturday, October 03, 2009 Saturday, October 03, 2009 1
Saturday, October 03, 2009 Sunday, October 04, 2009 1
Saturday, October 03, 2009 Monday, October 05, 2009 2
Saturday, October 03, 2009 Tuesday, October 06, 2009 3
Saturday, October 03, 2009 Wednesday, October 07, 2009 4
Saturday, October 03, 2009 Thursday, October 08, 2009 5
Saturday, October 03, 2009 Friday, October 09, 2009 6
Saturday, October 03, 2009 Saturday, October 10, 2009 6
Saturday, October 03, 2009 Sunday, October 11, 2009 6


Start_Date End_Date Workdays
Sunday, October 04, 2009 Sunday, October 04, 2009 1
Sunday, October 04, 2009 Monday, October 05, 2009 2
Sunday, October 04, 2009 Tuesday, October 06, 2009 3
Sunday, October 04, 2009 Wednesday, October 07, 2009 4
Sunday, October 04, 2009 Thursday, October 08, 2009 5
Sunday, October 04, 2009 Friday, October 09, 2009 6
Sunday, October 04, 2009 Saturday, October 10, 2009 6
Sunday, October 04, 2009 Sunday, October 11, 2009 6

Clifford Bass

:

You should not update a field with this information. You should calculate it
only when you need it for a report or a form. It violates database
normalization rules and has a good chance of getting out of sync and being
incorrect.

Here is how I would go about it.
First, here is a function that returns the number of working days between
two dates:

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHoliday", _
"[holidate] between " & Format(dtmStart, "\#yyyy\-mm\-dd\#") & _
" And " & Format(dtmEnd, "\#yyyy\-mm\-dd\#"))

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

------
Note the reference to the Holiday table. That is a table I use to be able
to know what weekdays should not be counted. Don't put any Saturday or
Sunday holidays it the table or it will throw the count off. If you don't
want to consider holidays, just remove this part:

'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHoliday", _
"[holidate] between " & Format(dtmStart, "\#yyyy\-mm\-dd\#") & _
" And " & Format(dtmEnd, "\#yyyy\-mm\-dd\#"))

Here is a sample query that will return the number of days booked and the
number of days not booked for each vehicle:

SELECT tblClient.MainName, Count(tblClient.MainName) AS NameCount,
CalcWorkDays(#10/1/2009#,#10/31/2009#)-[NameCount] AS DaysNotBooked
FROM tblClient
WHERE (((tblClient.EnteredOn) Between #1/1/1980# And #12/31/2010#))
GROUP BY tblClient.MainName;

You will need to change all the names. I threw this together with a table I
use for testing. Of course you can use parameters or reference an open form
control to establish the date range for the Between operator.