asked on

Need to convert Excel formula to Access function that determine number of Fridays in any given month/ year!

Need a Microsoft Access VBA function that returns number of Fridays in any given month of any given year.
Would like the answer based on the working solutions Tikas A Planck or Aladin that provided at this link:

https://www.mrexcel.com/forum/excel-questions/10553-how-many-fridays-any-given-month.html

I know it's just a matter of converting Excel VBA to a function like so: but I don't understand the syntax of the formula for Excel..

Public Function NumberOfFridaysInDate(dNow as Date) as Long


End Function

Open in new window

ste5an

It's a simple concatenation of functions. Do the same in VBA. Your friends are DatePart() and DateSerial().

ASKER CERTIFIED SOLUTION

Fabrice Lambert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

stephenlecomptejr

ASKER

I appreciate you Fabrice Lambert posting the function as requested.
However when I do this:

Debug.Print NumberOfFridaysInDate(12/11/2018)

Open in new window

I get 5 as an answer and there are only 4 Fridays in this month.

aikimark

in any given month/ year

You can tweak Fabrice's routine to accept two parameters. It would resemble this:

Public Function NumberOfFridaysInDate(parmMM, parmYYYY) As Long
    Dim begin As Date
    begin = DateSerial(parmYYYY, parmMM, 1)
    
    Dim finish As Date
    finish = DateSerial(parmYYYY, parmMM + 1, 0)
    
    Dim NumberOfFridays As Long        '// Byte or Integer is enough IMO
    NumberOfFridays = 0
    
    Dim dt As Date
    For dt = begin To finish
        If (Weekday(dt) = vbFriday) Then
            NumberOfFridays = NumberOfFridays + 1
        End If
    Next
    NumberOfFridaysInDate = NumberOfFridays
End Function

Open in new window

aikimark

You need to cast the value as a date:

Debug.Print NumberOfFridaysInDate(#12/11/2018#)

Open in new window

Norie

There were 5 Fridays in December 1899.

12/11/2018 is not regarded as a date, it's regarded as a calculation with the result of approximately 0.000541.

When treated as a date 0.000541 translates to 00:00:39 30 Dec 1899.

Try this,

Debug.Print NumberOfFridaysInDate('12/11/2018#)

the # tells Access to treat the value as a date.

stephenlecomptejr

ASKER

Appreciate all the replies - especially Fabrice's

Fabrice Lambert

12/11/2018 is not regarded as a date

More precisely, this is 12 / (11 / 2018)
12 divided by 11 divided by 2018