How Many Named Days in a month

[Michael Humby]

Happy New Year All

You may at some time need to calculate the amount of any given days in any given month. As in 'How many Fridays in March 2001' I need to do this often and this is my work around.

Create two UDF's 'Firstdow' and 'Lastdow'

function Firstdow as D(dateval as D,day_num as n)
temp=dateval-day(dateval)+1 'is first dateval month
Firstdow=temp+7-mod(dow(temp+6-day_num),7)-1
end function

function Lastdow as D(dateval as D,day_num as n)

temp=Addmonths(dateval-day(dateval)+1,1)
Lastdow=temp-mod(dow(temp+6-day_num),7)-1
end function

Combining the two functions together as below will give me 5 Fridays in the month of March 2001 if Date_field is any valid date in March 2001.

(Lastdow(Date_field,6)-Firstdow(Date_field,6)+7)/7 >Result 5

I would welcome a trimmed down solution to my work around but,in the meantime, hope this helps.

Michael Humby

[Tom Patten]

RE: How Many Named Days in a month

Michael,

Good job. I struggled with this and used a FirstOfMonth()UDF offered, I think, by Ira, added 28 days to it, figuring there were four of these days, plus, DaysInMonth(EndOfmonth-FirstOfMonth+1)-28 give me the number of days that were more than 4 in a month. etc... you get the idea. Not very clean, but eventually I got it to work, then I found it wasn't needed! But next time, thanks to your work, it won't take me near as long.

Tom

[Thomas Henkel]

RE: How Many Named Days in a month

WE HAD A SIMILAR PROBLEM WHEN WE FIRST SET OUR SYSTEMS UP. We needed to know how many of each type of day there were in any given month. Instead of doing it on-th-fly, I set up a database (table) called daymonth.dbf. This database contains dates for the first of every month since 1994 (when we started this mess). As our providers did not always get to us in a timely fashion, I thought it would be better to set up a static database showing the month and year then showing the number of days in each day type. ie: M T W th F SAT Sun.

Attached is the database and application card (ver1.02) that generates the counts. It may seem somewhat unsophisticated, but it works for us. When our billig application needs to know how many wednesdays there arein April of 2006 , we just do a lookup to the file and retrieve the proper field. This has been a real lifesaver.

Hope it helps.

Tom Henkel
Bergen County (NJ) Board of Social Services

[Thomas Henkel]

RE: How Many Named Days in a month

Just updated to ver 4.5. Includes counts out through 2019.

Added some other fancy things, like finding out last year, then adding 10 more. Also doesn't update entire file, only newly added ones.

Handles leap years just fine. This didn't work properly in ver1.02.

Tom Henkel