Function that finds number of Fridays in a particular interval date

[birdman300]

Hi everyone,

Here is the info the parent thread instructed me to provide.

1. Version of the program

Office 2010


2. What you want it to do

I am trying to write a function that finds the number of Fridays from 1 Jan 2001 to 31 Dec 3000

3. Cell references, bookmark names, column letters, row numbers, worksheets, styles, whatever pertains to the information at hand


I am still fairly new to VBA, so I am not trying to insert it into an Excel workbook or anything like that but just to get better at programming / VBA in general


4. Error messages if any

Just need more help in finishing what little code I have.

5. If not the entire code, then at least some of it


Public Function getFridays() As Long

'not sure what other variables I need but maybe one to hold the sum of fridays and a counter of some kind
Dim lngStartDate As Long
Dim lngEndDate As Long


lngStartDate = #1 Jan 2001#
lngEndDate = #31 Dec 3000#

'Do the built in date functions factor in leap years? If not, how do I check for them?
'I am pretty sure I need a loop to iterate through the dates but not sure how to start it

End Function


6. Sample data (before and after sample worksheets, add as attachments here)

Not applicable


7. Politeness and gratitude :)

Thank you in advance! Still new to all of this but slowly getting better


8. Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.

Of course

[SamT]

Welcome to VBA Express, Birdman.

You will need an Office application to write your code in. IMO, Excel is the easiest for beginners. Open Excel and Right Click the mouse on one of the Sheet tabs. Select "View Code." This opens the VB Editor or VBE. First, lets customize the VBE for you. You can also press Alt+F11.

Check the Menu >> Tools >> Options >> Editor >> Code Settings >> Auto Syntax. (What does that mean?) Well that is pretty much the way we guide people to a specific action in the menus around here.

It means, on the Menu bar of the VBE,
Select or click the Tools menu,
Then select or click the Options sub menu,
Then select or click or on the "Editor" Sub menu or Tab or Frame or whatever (Tab in this case.)
Then on the Code Settings Frame,
Check the Auto Syntax Check Box.

While you are on the Editor tab, I personally Check every box except "Drag and Drop Text Editing," and I prefer an Auto Indent setting of 2. Some prefer 3 and a very few like 4.

The Editor Format Tab lets you customize the text colors, sizes, and fonts used in the VBE.
Insure there is a check in the Margin Indicator Bar box. Clicking the MIB lets you set a BreakPoint at that line of code. Very handy.
On the General Tab, the Form Grid Settings Frame is used with User Forms, a subject for later. In the meantime, check all the Checkboxes in all the other Frames and select the Break on all Errors Option Button. On the Docking Tab, I check everything because I like my windows to stay in place.

The Help button will tell you what all those options do.

Continued:

[SamT]

Use of the VBA Express Editor. If you don't see any tool tips when you hover over the menu, the Icons are from left to right

A/A :=Toggle editor source mode
AXA :=Remove text formatting from selection
Folder or Briefcase + a notepad := Paste as plain text. Use this when you copy code from one post to another.
Folder or Briefcase + W : = Paste from MS Word.
Font w/Dropdown :=Change or set the Font used
Size w/Dropdown : = Change or set Font size
A w/Dropdown : = Font Color
Happy Face := Smilies, choose and click a smiley to insert it at the cursor.
Globe w/Chain Link := Insert Hyperlink behind Selected text.
Globe w/X :=remove Hyperlink from Selected text
# := Put Selected text inside code tags or put code tags on editor page for pasting code inside of. Code tags will auto format your code. You can manually type them. Edit a post that you have used them in to see how.
Cartoon Style Text balloon := Same as Code tags, but for Quote tags. Formats text a bit differently than standard.


Underneath the Post Editor, you will see the Go Advanced Button, which takes you to the Advanced Editor with many more formatting Options and under that, will be the Manage Attachments button for uploading files. You must make 3 or 4 posts first, then the next day, you can upload, or something like that.

Sometimes you want to emulate Rows and Columns or a Table in a post. The easiest way is to do it in Excel, then just copy it and paste it into the VBAX post Editor. You can use the Advanced Editor to insert tables or edit ones you pasted in. Don't try to align text vertically to fake it.

Continued.

[SamT]

Thank you for reading How to Get the Best Help Possible... (http://www.vbaexpress.com/forum/showthread.php?6-How-to-Get-the-Best-Help-Possible-Quickly&goto=newpost)


I am trying to write a function that finds the number of Fridays from 1 Jan 2001 to 31 Dec 3000

Public Function getFridays() As Long

'not sure what other variables I need but maybe one to hold the sum of fridays and a counter of some kind
Dim lngStartDate As Long
Dim lngEndDate As Long


lngStartDate = #1 Jan 2001#
lngEndDate = #31 Dec 3000#

'Do the built in date functions factor in leap years? If not, how do I check for them?
'I am pretty sure I need a loop to iterate through the dates but not sure how to start it
First, lets look at the

Dates and Times Keyword Summary




Action
Keywords


Get the current date or time.
Date, Now, Time


Perform date calculations.
DateAdd, DateDiff, DatePart


Return a date.
DateSerial, DateValue


Return a time.
TimeSerial, TimeValue


Set the date or time.
Date, Time


Time a process.
Timer




DateDiff looks promising, after all the number of fridays is almost equal to the difference in days divided by 7

[SamT]

DateDiff Function


Returns a Variant (Long) specifying the number of time intervals between two specified dates.
Syntax
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
The DateDiff function syntax has these named arguments:


Part
Description


interval
Required. String expression that is the interval of time you use to calculate the difference between date1 and date2.


date1, date2
Required; Variant (Date). Two dates you want to use in the calculation.


firstdayofweek
Optional. A constant that specifies the first day of the week. If not specified, Sunday is assumed.


firstweekofyear
Optional. A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.



Settings
The interval argument has these settings:


Setting
Description


yyyy
Year


q
Quarter


m
Month


y
Day of year


d
Day


w
Weekday


ww

Week


h
Hour


n
Minute


s
Second



The firstdayofweek argument has these settings:


Constant
Value
Description


vbUseSystem
0
Use the NLS API setting.


vbSunday
1
Sunday (default)


vbMonday
2
Monday


vbTuesday
3
Tuesday


vbWednesday
4
Wednesday


vbThursday
5
Thursday


vbFriday

6
Friday


vbSaturday
7
Saturday





Constant
Value
Description


vbUseSystem
0
Use the NLS API setting.


vbFirstJan1
1
Start with week in which January 1 occurs (default).


vbFirstFourDays
2
Start with the first week that has at least four days in the new year.


vbFirstFullWeek
3
Start with first full week of the year.



Remarks
You can use the DateDiff function to determine how many specified time intervals exist between two dates. For example, you might use DateDiff to calculate the number of days between two dates, or the number of weeks between today and the end of the year.
To calculate the number of days between date1 and date2, you can use either Day of year ("y") or Day ("d"). When interval is Weekday ("w"), DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1. If interval is Week ("ww"), however, the DateDiff function returns the number of calendar weeks between the two dates. It counts the number of Sundays (First day of week. st) between date1 and date2. DateDiff counts date2 if it falls on a Sunday; but it doesn't count date1, even if it does fall on a Sunday.

If date1 refers to a later point in time than date2, the DateDiff function returns a negative number.
The firstdayofweek argument affects calculations that use the "w" and "ww" interval symbols.

If date1 or date2 is a date literal, the specified year becomes a permanent part of that date. However, if date1 or date2 is enclosed in double quotation marks (" "), and you omit the year, the current year is inserted in your code each time the date1 or date2 expression is evaluated. This makes it possible to write code that can be used in different years.
When comparing December 31 to January 1 of the immediately succeeding year, DateDiff for Year ("yyyy") returns 1 even though only a day has elapsed.
Note For date1 and date2, if the Calendar property setting is Gregorian, the supplied date must be Gregorian. If the calendar is Hijri, the supplied date must be Hijri.

[SamT]

DateValue Function
Returns a Variant (Date).
Syntax
DateValue(date)
The required date argument is normally a string expression representing a date from January 1, 100 through December 31, 9999. However, date can also be any expression that can represent a date, a time, or both a date and time, in that range.
Remarks
If date is a string that includes only numbers separated by valid date separators, DateValue recognizes the order for month, day, and year according to the Short Date format you specified for your system. DateValue also recognizes unambiguous dates that contain month names, either in long or abbreviated form. For example, in addition to recognizing 12/30/1991 and 12/30/91, DateValue also recognizes December 30, 1991 and Dec 30, 1991.
If the year part of date is omitted, DateValue uses the current year from your computer's system date.
If the date argument includes time information, DateValue doesn't return it. However, if date includes invalid time information (such as "89:98"), an error occurs.

[birdman300]

Hello SamT and thank you for the help. I think I figured out a working VBA function:


Public Function GetCountFriday() As Long
Dim dtBegin As Date
Dim dtEnd As Date
Dim strInterval As String
Dim lngFridayCount As Long
dtBegin = #1/1/2001#
dtEnd = #12/30/3000#
strInterval = "d"
lngFridayCount = DateDiff(strInterval, dtBegin, dtEnd, vbFriday) / 7
GetCountFriday = lngFridayCount

End Function

[SamT]

In order to make that a working function, you need a way to put different dates in and get different answers out.


Option Explicit

Public Function GetCountFriday(dtBegin As Date, dtEnd As Date) As Long
Dim strInterval As String
Dim lngFridayCount As Long

strInterval = "d"

lngFridayCount = DateDiff(strInterval, dtBegin, dtEnd, vbFriday) / 7
GetCountFriday = lngFridayCount

End Function
You can Run a Sub with the Run Menu, or by putting the cursor inside a Sub and pressing F5. You can Step through a Sub by pressing F8. You cannot do that with a Function.

If you Step through a Sub that uses a Function, you will Step through the Function.

Sub Test_GetCountFriday()
Dim X, B, E

B = CDate("1 Jan 2001")
E = CDate("30 Dec 3000")

X = GetCountFriday(B, E)
Debug.Print (X)
End Sub
Note that in Test Stubs, which are only ever Run during development, only the minimum required to test the subject is written. This provides a visual clue that the Stub is not part of the working code.

My crystal ball says that the count is off by 1 and that the FirstDayOf Week argument is not helping.
You need to add a step to the function, or change the interval.

Sub Test_GetCountFriday()
'Function should return 2
Dim X, B, E

B = CDate("18 Dec, 2014")
E = CDate("27 Dec, 2014")

X = GetCountFriday(B, E)
Debug.Print (X)

End Sub

[Bob Phillips]

Hello SamT and thank you for the help. I think I figured out a working VBA function:


Public Function GetCountFriday() As Long
Dim dtBegin As Date
Dim dtEnd As Date
Dim strInterval As String
Dim lngFridayCount As Long
dtBegin = #1/1/2001#
dtEnd = #12/30/3000#
strInterval = "d"
lngFridayCount = DateDiff(strInterval, dtBegin, dtEnd, vbFriday) / 7
GetCountFriday = lngFridayCount

End Function

All you need is



Public Function GetCountFriday(ByVal Date1 As Range, ByVal Date2 As Range) As Long
GetCountFriday = DateDiff("d", Date1, Date2, vbFriday)
End Function


where the dates are passed as cell references.

But you can do it all with a formula

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(--"2015-01-01"&":"&--"2015-12-31")))=6))

or

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(M1&":"&M2)))=6))

[birdman300]

Thank you everyone for the help! I have a working solution now!

[SamT]

:thumb

Share it with us??

[birdman300]

I just changed the interval from "d" to "ww", which gives me the total fridays in each month:


Public Function GetCountFriday() As Long
Dim dtBegin As Date
Dim dtEnd As Date
Dim blnDateCheckBegin As Boolean
Dim blnDateCheckEnd As Boolean

dtBegin = DateSerial(2001, 1, 1)
dtEnd = DateSerial(2001, 3, 30)
'Check to ensure start date is a date
blnDateCheckBegin = IsDate(dtBegin)

'Check to ensure end date is a date
blnDateCheckEnd = IsDate(dtEnd)

'Retrieve number of Fridays that have fallen in a month per week using "ww"
GetCountFriday = DateDiff("ww", dtBegin, dtEnd, vbFriday)
End Function

[SamT]

I just changed the interval from "d" to "ww", which gives me the total fridays in each month:


Public Function GetCountFriday() As Long
Dim dtBegin As Date
Dim dtEnd As Date
Dim blnDateCheckBegin As Boolean
Dim blnDateCheckEnd As Boolean

dtBegin = DateSerial(2015, 1, 1)
dtEnd = DateSerial(2015, 3, 30)
'Check to ensure start date is a date
blnDateCheckBegin = IsDate(dtBegin)

'Check to ensure end date is a date
blnDateCheckEnd = IsDate(dtEnd)

'Retrieve number of Fridays that have fallen in a month per week using "ww"
GetCountFriday = DateDiff("ww", dtBegin, dtEnd, vbFriday)
End Function
Using "ww" is the best way to count particular days in a period of time.

I don't have a calendar for 2001, so I changed the dates to 2015. The above code returns 13 Fridays in Jan, Feb and Mar of 2015.

How many Fridays does it count if the start date is 2015, 1, 2? Why the difference?

If the end Date is 2015, 2, 30, what is the value of dtEnd? Why

If the End Date is 2015, 99, 99, what is the value of blnDateCheckEnd?

What will blnDateCheckEnd do to the Function if it's value is "False?"? What will the Function Return?

Do you like this Platonic method of learning? Would you prefer "By Example" instead? Do you have questions or requests?

VBA HINT: Set a Breakpoint at "End Function" by clicking the wide vertical border next to the "E" in End. This will stop processing there. Then Hover the mouse over each variable to see their values at that point. Then press F5 or F8 again.

BTW, I fixed the typos in my post # 2 above.

[snb]

try this:


Sub M_snb()
msgbox getcountfriday("01/01/2015","03/30/2015")
end sub

Public Function GetCountFriday(c01 as date,c02 as date)
GetCountFriday = DateDiff("ww", c01, c02, 6)
End Function