Count # of Fridays between start & end date

[leebo]

Hi,

I have a table with a start & end date relating to employee holidays. I have calculated the number of working days between the range but I also wish to return the number of Fridays too.

For instance - Lee - 26/03/09 - 04/04/09 - 7 (working days) - 2 (fridays)

Im stumped!
Any ideas?
Thanks,
L

 

[gemma-the-husky]

tricky - heres a function that should do it - the code operation should be pretty clear

[aircode for you - edited now - should have been wend, not end function]

function fridays(byval startdate, byval enddate) as long
dim wholeweeks as long

wholeweeks=0

while weekday(startdate)<>vbfriday
  startdate=startdate+1
[COLOR="Red"]wend[/COLOR]

while weekday(enddate)<>vbfriday
  enddate=enddate-1
[COLOR="red"]wend[/COLOR]

while startdate<enddate
  startdate=startdate+7
  wholeweeks=wholeweeks+1
wend

fridays = wholeweeks
end function

 

[leebo]

Hi Dave,

Thanks for your quick response, could you advise how this would be implemented. I'm fairly ok with simple macros, but this is a little out of my comfort zone..
Rgds,
L

 

[gemma-the-husky]

put the function in a module

then in a query based on your table, you can add a column

fridaycount: fridays(startdate, enddate)

thats it

 

[raskew]

Hi -

Think you might have gotten a couple of typos there:

.....

[COLOR="red"]While[/COLOR] WeekDay(startdate) <> vbFriday
  startdate = startdate + 1
[COLOR="Red"]End Function[/COLOR]

[COLOR="red"]While[/COLOR] WeekDay(enddate) <> vbFriday
  enddate = enddate - 1
[COLOR="red"]End Function[/COLOR]

While startdate < enddate
  startdate = startdate + 7
  wholeweeks = wholeweeks + 1
Wend

.....

Bob

 

[Mike375]

But is it basically OK. A couple of extra Wend a couple of less End Function. Works in attahced on Query1

 

[Mike375]

Attached did not open. Try again

Can't open the attached.

Public Function fridays(ByVal startdate, ByVal enddate) As Long
Dim wholeweeks As Long
wholeweeks = 0
While Weekday(startdate) <> vbFriday
startdate = startdate + 1

While Weekday(enddate) <> vbFriday
enddate = enddate - 1

While startdate < enddate
startdate = startdate + 7
wholeweeks = wholeweeks + 1
Wend
fridays = wholeweeks
Wend
Wend
End Function

Exp: fridays(,[F]) for query with being the name of the field with starting date, as per Gemma said

 

[Mike375]

Try attaching system again.

Still can't open after changing name. Can others open the attached.

I think I have something wrong at my end because sometimes I can't open Zip attachments unless I save the Zip to disk and open from there.

 

[Mike375]

A bug in the code I used is if the finishing date is a Friday then it gives 0 as the result

 

[raskew]

Hi -

Give this a try. I used the quoted start/end dates as examples since they are easy to
view/verify on a calendar.

Public Function CountDays(ByVal sDte As Date, eDte As Date, iday As Integer) As Integer
'Purpose:  Count number of specified weekdays (e.g. vbMonday (or 2) occuring in a
'          specified date range
'Coded by: raskew
'Inputs: (Note: 2 can be substituted for vbMonday, 5 for vbThursday)
'       1) ? CountDays(#3/26/09#, #4/4/09#, vbMonday)
'       2) ? CountDays(#3/26/09#, #4/4/09#, vbThursday)
'Outputs:
'       1) 1
'       2) 2

Dim WeekHold  As String
Dim WeekKeep  As String
Dim FullWeek  As Integer
Dim OddDays   As Integer
Dim n         As Integer

    WeekHold = "1234567123456"

    'get # of full weeks (7 days) 
    FullWeek = Int((eDte - sDte + 1) / 7)

    'get # of days remaining after FullWeek is determined
    OddDays = (eDte - sDte + 1) Mod 7

    'get display of weekdays remaining
    WeekKeep = Mid(WeekHold, WeekDay(sDte), OddDays)

    'determine if iday exists in WeekKeep
    n = Abs(InStr(WeekKeep, iday) > 0)

    'there's a count of 1 for each FullWeek + the possibility
    'of one additional day in OddDays

    CountDays = FullWeek + n

End Function

If desired, this logic (minus the function) could be put into one big-ole yucky statement, e.g.:
(from the debug (immediate) window)

s = #3/26/09#
e = #4/4/09#

'count Thursdays
wd = 5
? ((e - s + 1) \ 7) + abs(InStr(Mid("1234567123456", WeekDay(s), (e - s + 1) Mod 7), wd)>0)
2

'count Mondays
wd = 2
? ((e - s + 1) \ 7) + abs(InStr(Mid("1234567123456", WeekDay(s), (e - s + 1) Mod 7), wd)>0)
1

HTH - Bob

 

[Mike375]

Thats got it Bob.

Exp: CountDays(,[F],6)

 

[raskew]

Hey -

Glad it worked for you. Realize the logic is a tad bit difficult to follow, but it seems to do the job.

Best Wishes - Bob

 

[Mike375]

It includes (eg friday) both Fridays in the count if Friday is both the start and finish date. But if only "between" was required that would be easy to do.

20/03/2009 and 27/03/2009 gives 2

20/03/2009 and 26/03/2009 gives 1

21/03/2009 and 27/03/2009 gives 1

 

[raskew]

Hi -

It includes (eg friday) both Fridays in the count if Friday is both the start and finish date. But if only "between" was required that would be easy to do.

20/03/2009 and 27/03/2009 gives 2

My understanding of 'between', e.g. 'between 20-Mar-2009 and 27-Mar-2009', is that 20 March is the lower limit while 27 March is that the upper limit. So, in this respect it's functioning as designed. What was not intended was something like only 21, 22, 23, 24, 25 & 26 March would be counted.

It's akin to saying '...pick a number between 1 and 5'. The idea there, at least in US dialect, is that you could pick 1, 2, 3, 4, or 5 and not just 2, 3 or 4.

What do you think?

Bob

 

[leebo]

Thanks guys thats sorted it out....
Much appreciated,
Have great weekends.
L

 

[Mike375]

Hi -


My understanding of 'between', e.g. 'between 20-Mar-2009 and 27-Mar-2009', is that 20 March is the lower limit while 27 March is that the upper limit. So, in this respect it's functioning as designed. What was not intended was something like only 21, 22, 23, 24, 25 & 26 March would be counted.

It's something like saying '...pick a number between 1 and 5'. The idea there, at least in US dialect, is that you could pick 1, 2, 3, 4, or 5 and not just 2, 3 or 4.

What do you think?

Bob

I guess it can be taken either way. Normal use of "between" would probably exclude Fridays (or whichever day was to be used) which were on either the starting or finishing date. On the other hand if the number of Fridays was for pay days etc then you would count those that were on either the start/finish date.

A bit of juggling with IIF and have columns for both answers

 

[Mike375]

I had not used Switch for some time and changing CountDays to CD

Switch(Weekday()=6 And Weekday([F])=6,CD(,[F],6)-2,Weekday()=6 And Weekday([F])<>6,CD(,[F],6)-1,Weekday()<>6 And Weekday([F])=6,CD(,[F],6)-1,Weekday()<>6 And Weekday([F])<>6,CD(,[F],6))

 

[gemma-the-husky]

sorry guys

thanks for pointing out the coding errors

i've amended the code in the original post - i didnt test it, but i'm sure it will work

One other point is that it doesnt deal with null dates either.