Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How can I count how many "Saturday" between tow dates?
- Thread starter ALSayegh
- Start date
Following Function generalized your request to any day of the week (WhichDay argument). It also includes an optional parameter(IntervalLimits) that resolves the ambiguity created by the "between" specification: What happens if the first day of the interval is the specified day (e.g. Saturday). Is it included in "between" or not?
Private Enum DateScope
StartIncluded
EndIncluded 'Default: always included
End Enum
Public Sub main()
Dim datStart As Date, DatEnd As Date
datStart = InputBox("Start"
DatEnd = InputBox("end"
Dim intSaturdays As Integer
intSaturdays = HowManyDays(datStart, DatEnd, vbSaturday)
Debug.Print DatEnd, datStart, intSaturdays
'Include first day of the interval
intSaturdays = HowManyDays(datStart, DatEnd, vbSaturday, StartIncluded)
Debug.Print DatEnd, datStart, intSaturdays
End Sub
Private Function HowManyDays(datStart As Date, DatEnd As Date, WhichDay As Long, _
Optional IntervalLimits As DateScope = EndIncluded) As Integer
Dim intWeeks As Integer
intWeeks = DateDiff("ww", datStart, DatEnd, WhichDay)
Select Case IntervalLimits
Case StartIncluded
HowManyDays = intWeeks + 1
Case EndIncluded
HowManyDays = intWeeks
End Select
End Function
_________________________________
In theory, there is no difference between theory and practice. In practice, there is. [attributed to Yogi Berra]
Private Enum DateScope
StartIncluded
EndIncluded 'Default: always included
End Enum
Public Sub main()
Dim datStart As Date, DatEnd As Date
datStart = InputBox("Start"
DatEnd = InputBox("end"
Dim intSaturdays As Integer
intSaturdays = HowManyDays(datStart, DatEnd, vbSaturday)
Debug.Print DatEnd, datStart, intSaturdays
'Include first day of the interval
intSaturdays = HowManyDays(datStart, DatEnd, vbSaturday, StartIncluded)
Debug.Print DatEnd, datStart, intSaturdays
End Sub
Private Function HowManyDays(datStart As Date, DatEnd As Date, WhichDay As Long, _
Optional IntervalLimits As DateScope = EndIncluded) As Integer
Dim intWeeks As Integer
intWeeks = DateDiff("ww", datStart, DatEnd, WhichDay)
Select Case IntervalLimits
Case StartIncluded
HowManyDays = intWeeks + 1
Case EndIncluded
HowManyDays = intWeeks
End Select
End Function
_________________________________
In theory, there is no difference between theory and practice. In practice, there is. [attributed to Yogi Berra]
edderic
Programmer
- May 8, 1999
- 628
- 0
- 0
Or :
Dim dstart As Date
Dim dstop As Date
Dim i As Integer
Dim intCount As Integer
Dim MyDate As Variant
dstart = "01/09/2001"
dstop = "30/09/2001"
For i = 1 To 1 + DateDiff("d", dstart, dstop)
MyDate = DateSerial(Year(dstart), Month(dstart), Day(dstart) + i - 1)
If DatePart("w", MyDate) = 7 Then
intCount = intCount + 1
End If
Next i
MsgBox "Total saturdays :" & intCount Eric De Decker
vbg.be@vbgroup.nl
Dim dstart As Date
Dim dstop As Date
Dim i As Integer
Dim intCount As Integer
Dim MyDate As Variant
dstart = "01/09/2001"
dstop = "30/09/2001"
For i = 1 To 1 + DateDiff("d", dstart, dstop)
MyDate = DateSerial(Year(dstart), Month(dstart), Day(dstart) + i - 1)
If DatePart("w", MyDate) = 7 Then
intCount = intCount + 1
End If
Next i
MsgBox "Total saturdays :" & intCount Eric De Decker
vbg.be@vbgroup.nl
MichaelRed
Programmer
rvBasic,
I do not quite get the reason for the emnum. If enddate is default always included, then is appears that the only purpose of the enum is to support the inclusion of the start date, which colud easily be done with a simple boolean? coluld you elaborate on what I'm missing here/
MichaelRed
mred@att.net
There is never time to do it right but there is always time to do it over
I do not quite get the reason for the emnum. If enddate is default always included, then is appears that the only purpose of the enum is to support the inclusion of the start date, which colud easily be done with a simple boolean? coluld you elaborate on what I'm missing here/
MichaelRed
mred@att.net
There is never time to do it right but there is always time to do it over
- Thread starter
- #5
MichaelRed
Programmer
For 'lightweight' work it is probably inconsequential, but rvBasics soloution is MUCH more efficient. The question I asked of him is somewhat technical, and should not be confused with a critique or even a suggestion for change. If your app were to make extensive use of this procedure, ond /or calculate the # of (Satur]days over a long period, you will fine eddrics method considerable more resource intensive.
MichaelRed
mred@att.net
There is never time to do it right but there is always time to do it over
MichaelRed
mred@att.net
There is never time to do it right but there is always time to do it over
MichaelRed
Programmer
Eric,
Your'e quite welcome, although the response was directed toward ALSayegh. However since you are reading the thread, I wouold STILL like the rationale.
MichaelRed
mred@att.net
There is never time to do it right but there is always time to do it over
Your'e quite welcome, although the response was directed toward ALSayegh. However since you are reading the thread, I wouold STILL like the rationale.
MichaelRed
mred@att.net
There is never time to do it right but there is always time to do it over
Dear Michael,
The rationale is ... laziness, pure programmer's laziness.
Because of the ambiguity caused by the word between I envisioned several possible values for the DateScope Enum. If I remember well old schoolday algebra, there are different types of intervals on the numeric scale: open, closed and half-open (left, right) depending on whether the boundaries were included or not.
After the facts, AlSayegh's reply to edderic seems to imply that he meant a closed interval.
My the original plan was to make allowance for the four possibilities. Very rapidly however the code became too strangled for the time I had available at that moment. So I dropped some constants out of the Enum, thereby reducing it to two values, one of which was choosen as default...
That being said, the simplified code above works correctly ... most of the time. In other words the code is flawed! A erroneous result is produced if Start date = End date = WhichDay (This is catched correctly by edderic's algorithm.
We also both assumed that End date had to be greater than Start date, which in normal spoken language is not necessarily the case. It is perfectly valid to ask: "How many saterdays are there between September 30 and september 1st?". So I decided to bite the bullet and the resulting comprehensive algorithm is produced below. Despite its length, the code is still faster than edderic's if the dates are sufficiently far apart.
Please not some changes in the naming of variables and default values. So far, my testing did not produce invalid results.
Private Const c_intDaysInWeek As Integer = 7
Private Enum DateInterval
NoneIncluded 'Neither Begin nor End included, Open Interval
BeginIncluded 'Begin included, End Not
EndIncluded 'End included, Begin not
BothIncluded 'Default Both Begin and End included, Closed Interval
End Enum
Private Function HowManyDaysOfWeek(datBegin As Date, datEnd As Date, WhichDay As Integer, _
Optional IntervalLimits As DateInterval = BothIncluded) As Integer
'* Purpose : Determines the number of DayOfWeek between two given dates
'* e.g. How many Saterdays are there between Jan 1st and August 15th?
'* Accepts : datBegin - First day of interval. e.g. Jan 1st, 2001
'* datEnd - Last day of interval. e.g. August 15th, 2001
'* WhichDay - Which day of the week, expressed as a VB System Constant
'* e.g. vbSaturday
'* IntervalLimits - an optional Enumerated variable of type DateInterval that
'* specifies if and which of the First or Last day are to be included in the
'* calculation. Possible values are: NoneIncluded, BeginIncluded, EndIncluded
'* and BothIncluded (Default value)
'* Returns : A positive or zero integer if calculation was successful
'* -1 if WhichDay is outside valid range of days
'* -2 if IntervalLimits is outside acceptable values
If WhichDay < vbSunday Or WhichDay > vbSaturday Then
HowManyDaysOfWeek = -1
GoTo PROC_EXIT
End If
If IntervalLimits < NoneIncluded Or IntervalLimits > BothIncluded Then
HowManyDaysOfWeek = -2
GoTo PROC_EXIT
End If
Select Case datEnd >= datBegin
Case True
Select Case Weekday(datBegin) = WhichDay
Case True
Select Case datEnd = datBegin
Case True
Select Case IntervalLimits
Case BothIncluded, BeginIncluded, EndIncluded
HowManyDaysOfWeek = 1
Case NoneIncluded
HowManyDaysOfWeek = 0
End Select
Case False 'datEnd <> datBegin
Select Case (datEnd - datBegin) Mod c_intDaysInWeek
Case Is = 0 'Interval = exact number of weeks
Select Case IntervalLimits
Case BothIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd) + 1
Case BeginIncluded, EndIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd)
Case NoneIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd) - 1
End Select
Case Is > 0 'Interval <> exact number of weeks
Select Case IntervalLimits
Case BothIncluded, BeginIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd) + 1
Case EndIncluded, NoneIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd)
End Select
End Select
End Select
Case False 'datBegin <> WhichDay
Select Case Weekday(datEnd) = WhichDay
Case True
Select Case IntervalLimits
Case BothIncluded, EndIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd) + 1
Case BeginIncluded, NoneIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd)
End Select
Case False 'datEnd <> WhichDay
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd)
End Select
End Select
Case False 'datend < datBegin
Select Case IntervalLimits
Case NoneIncluded, BothIncluded
HowManyDaysOfWeek = HowManyDaysOfWeek(datEnd, datBegin, WhichDay, IntervalLimits)
Case BeginIncluded
HowManyDaysOfWeek = HowManyDaysOfWeek(datEnd, datBegin, WhichDay, EndIncluded)
Case EndIncluded
HowManyDaysOfWeek = HowManyDaysOfWeek(datEnd, datBegin, WhichDay, BeginIncluded)
End Select
End Select
PROC_EXIT:
Exit Function
End Function
Comments are wellcome! _________________________________
In theory, there is no difference between theory and practice. In practice, there is. [attributed to Yogi Berra]
The rationale is ... laziness, pure programmer's laziness.
Because of the ambiguity caused by the word between I envisioned several possible values for the DateScope Enum. If I remember well old schoolday algebra, there are different types of intervals on the numeric scale: open, closed and half-open (left, right) depending on whether the boundaries were included or not.
After the facts, AlSayegh's reply to edderic seems to imply that he meant a closed interval.
My the original plan was to make allowance for the four possibilities. Very rapidly however the code became too strangled for the time I had available at that moment. So I dropped some constants out of the Enum, thereby reducing it to two values, one of which was choosen as default...
That being said, the simplified code above works correctly ... most of the time. In other words the code is flawed! A erroneous result is produced if Start date = End date = WhichDay (This is catched correctly by edderic's algorithm.
We also both assumed that End date had to be greater than Start date, which in normal spoken language is not necessarily the case. It is perfectly valid to ask: "How many saterdays are there between September 30 and september 1st?". So I decided to bite the bullet and the resulting comprehensive algorithm is produced below. Despite its length, the code is still faster than edderic's if the dates are sufficiently far apart.
Please not some changes in the naming of variables and default values. So far, my testing did not produce invalid results.
Private Const c_intDaysInWeek As Integer = 7
Private Enum DateInterval
NoneIncluded 'Neither Begin nor End included, Open Interval
BeginIncluded 'Begin included, End Not
EndIncluded 'End included, Begin not
BothIncluded 'Default Both Begin and End included, Closed Interval
End Enum
Private Function HowManyDaysOfWeek(datBegin As Date, datEnd As Date, WhichDay As Integer, _
Optional IntervalLimits As DateInterval = BothIncluded) As Integer
'* Purpose : Determines the number of DayOfWeek between two given dates
'* e.g. How many Saterdays are there between Jan 1st and August 15th?
'* Accepts : datBegin - First day of interval. e.g. Jan 1st, 2001
'* datEnd - Last day of interval. e.g. August 15th, 2001
'* WhichDay - Which day of the week, expressed as a VB System Constant
'* e.g. vbSaturday
'* IntervalLimits - an optional Enumerated variable of type DateInterval that
'* specifies if and which of the First or Last day are to be included in the
'* calculation. Possible values are: NoneIncluded, BeginIncluded, EndIncluded
'* and BothIncluded (Default value)
'* Returns : A positive or zero integer if calculation was successful
'* -1 if WhichDay is outside valid range of days
'* -2 if IntervalLimits is outside acceptable values
If WhichDay < vbSunday Or WhichDay > vbSaturday Then
HowManyDaysOfWeek = -1
GoTo PROC_EXIT
End If
If IntervalLimits < NoneIncluded Or IntervalLimits > BothIncluded Then
HowManyDaysOfWeek = -2
GoTo PROC_EXIT
End If
Select Case datEnd >= datBegin
Case True
Select Case Weekday(datBegin) = WhichDay
Case True
Select Case datEnd = datBegin
Case True
Select Case IntervalLimits
Case BothIncluded, BeginIncluded, EndIncluded
HowManyDaysOfWeek = 1
Case NoneIncluded
HowManyDaysOfWeek = 0
End Select
Case False 'datEnd <> datBegin
Select Case (datEnd - datBegin) Mod c_intDaysInWeek
Case Is = 0 'Interval = exact number of weeks
Select Case IntervalLimits
Case BothIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd) + 1
Case BeginIncluded, EndIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd)
Case NoneIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd) - 1
End Select
Case Is > 0 'Interval <> exact number of weeks
Select Case IntervalLimits
Case BothIncluded, BeginIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd) + 1
Case EndIncluded, NoneIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd)
End Select
End Select
End Select
Case False 'datBegin <> WhichDay
Select Case Weekday(datEnd) = WhichDay
Case True
Select Case IntervalLimits
Case BothIncluded, EndIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd) + 1
Case BeginIncluded, NoneIncluded
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd)
End Select
Case False 'datEnd <> WhichDay
HowManyDaysOfWeek = DateDiff("w", datBegin, datEnd)
End Select
End Select
Case False 'datend < datBegin
Select Case IntervalLimits
Case NoneIncluded, BothIncluded
HowManyDaysOfWeek = HowManyDaysOfWeek(datEnd, datBegin, WhichDay, IntervalLimits)
Case BeginIncluded
HowManyDaysOfWeek = HowManyDaysOfWeek(datEnd, datBegin, WhichDay, EndIncluded)
Case EndIncluded
HowManyDaysOfWeek = HowManyDaysOfWeek(datEnd, datBegin, WhichDay, BeginIncluded)
End Select
End Select
PROC_EXIT:
Exit Function
End Function
Comments are wellcome! _________________________________
In theory, there is no difference between theory and practice. In practice, there is. [attributed to Yogi Berra]
MichaelRed
Programmer
AS they 'say' in the legal trade. Never ask a question unless your'e SURE you want to know the answer! :~/
MichaelRed
mred@att.net
There is never time to do it right but there is always time to do it over
MichaelRed
mred@att.net
There is never time to do it right but there is always time to do it over
- Status
Similar threads
- Locked
- Question
