I have a Filemaker project that needs a function that determines the number
of days (Mondays, Tuesdays, etc.) of a specific month and year.

Given the parameters (June, 2003, Monday)
it would return the number of Mondays in June 2003.

Result: 5

A while back I raised this question in this newsgroup. I received several
suggestions for FileMaker 6, show below:

But I'd like to use a built-in FileMaker 7 function, if there is one.

I'm sure there's a simpler way in Filemaker ... maybe version 7 now has a
date or status function for this. But I'm not terribly familiar with
Filemaker¹s date format yet.


================================================== ===========
--- previously suggested solutions ---
================================================== ===========

MonthField (number) (Use 1 for January, 2 for February, etc. -- just like
the Month function)

YearField (number)

Weekday (number) (Use 1 for Sunday, 2 for Monday, etc. -- just like the
DayofWeek function)

CountWeekday (calculation, number) =

(
(Date( MonthField + 1, 0, YearField) - DayofWeek( Date( MonthField + 1, 0,
YearField)) + Weekday - (7 * (DayofWeek( Date( MonthField + 1, 0,
YearField)) < Weekday)))
-
(Date( MonthField, 1, YearField) - DayofWeek( Date( MonthField, 1,
YearField)) + Weekday + (7 * (DayofWeek( Date( MonthField, 1, YearField)) >
Weekday)))
) / 7 + 1


The first part of the formula determines the last selected weekday of the
selected month and year, the second part determines the first selected
weekday for that month. Subtract the first from the last (dates are stored
as a number of days from a starting point), divide by 7 and add 1.

================================================== ===========

Define 4 fields:

Year?
Month?
DayOfWeek?
CountOfDayOfWeek

.... the first three are input fields

The fourth a calc like this:

CountOfDayOfWeek=

If ( (DayOfWeek? = DayOfWeek(1+Date(Month?,28,Year?)) AND
Month? = Month (1+Date(Month?,28,Year?))
OR
(DayOfWeek? = DayOfWeek(2+Date(Month?,28,Year?)) AND
Month? = Month (2+Date(Month?,28,Year?))
OR
(DayOfWeek? = DayOfWeek(3+Date(Month?,28,Year?)) AND
Month? = Month (3+Date(Month?,28,Year?)),
5,4)

tried the following:


Translation: If any of the three expressions separated by ORs yields
True, then there are 5 of that day in the month of that year, otherwise
there are 4.

Note that "DayOfWeek?" is a field we are defining

"DayOfWeek()" is a FM function that accepts a Date and returns
a DayName. Similar with "Month?" and "Month()"

Why it works:

In any given month,we know there are 4 of each DayOfWeek in the first 28
days of the month. There may also be a fifth in the 29th, 30, or 31st day
of the month, assuming that month has 29, 30, or 31 days.

So for each of three dates beyond the 29th date in a month, we test to see
if the DayOfWeek of that date has the same name as our input field
"DayOfWeek?" and we test to see whether that date actually falls within
the given month.

Greg Dember <greg (AT) demberdatabase (DOT) com>

================================================== =========

One simple way, not necessarily the most elegant way, would be to create
a calc key that concatenated dayofweek, and month ofyear, and year
Left(DayName(date), 3) & " " & Month(date) & " " & Year(date)
you could drop the Left 3 bit that truncates the day name, and just use
DayName(date)

create a self rel key::key,
and a second calc using Count rel
Count(rel 01::date)

Variations would be use a switch to seta dayof week for a day specific left
key or individual global day names, and respective rels to...

I largely ignored the specific month and year bit, that should be reasonably
obvious

Chris Brown <cbrown (AT) medicine (DOT) adelaide.edu.au>

Chris Brown
Neurosurgery
University of Adelaide

========================

Thanks

In article <BD1C38C7.31FA3%John (AT) SingingWood (DOT) com>, "John B."
<John (AT) SingingWood (DOT) com> wrote:

It's pretty doubtful you'd ever get such an obscure function built in
to FileMaker Pro or even a plug-in (I don't think even the over-bloated
Excel has anything like that), so I'd go with one of the suggested
options or a script ... although I didn't read them to see if they'd
actually do what you wanted.

It's probably possible to use the day the month starts on, the day the
month ends on and the number of days in the month to calculate how many
of a particular day, but it'd be a pretty complex calculation and
probably no easier than the other suggestions. If I get time I'll have
a look at working this method out later today.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

John B. <John (AT) SingingWood (DOT) com> wrote:

You could write the calculation, and turn it into a custom function in
FM 7 with Developer. Then you can insert your custom function into any
file that needs it.

Basically, what you need to do is test each of the first 7 days of the
month to see what date the designated date falls on. Then you need to
test the length of the month, being 28, 29, 30 or 31 days. Then you can
figure out how many of that day is in the month.


Given fields called TestDay (the day you want to find), and fields that
derive the Month and Year of your target month, and MonthLength, pseudo
code for which is:

(Date (Month + 1, 1, Year) - 1) - Date(Month,1, Year) <adjust this for
december/january shifts with Case statements

Code for how many days is

Case(
DayName (Month, 1, Year) = TestDay and Monthlength > 28, 5,
DayName (Month, 2, Year) = TestDay and Monthlength > 29, 5,
DayName (Month, 3, Year) = TestDay and Monthlength > 30, 5, 4)

This should get you close to what you want. Works for both FM 6 and 7,
though in 7 you can build the custom function and just call it like any
other calculation function.



Lynn Allen
---
www.semiotics.com

In article <180720040924051522%helpful_harry (AT) nom (DOT) de.plume.com>, Helpful
Harry <helpful_harry (AT) nom (DOT) de.plume.com> wrote:

Now that I've had time to read the other suggestions you had I doubt I
can improve on either of the first two (or Lynn Allen's new one) - they
all look like they'll work exactly as you need and aren't THAT
complicated.

The third suggestion you listed won't do what you want. It will only
count the records witihin the database that have the same day entered
in a field.



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

John B. wrote:

John,

I have a couple of similar requirements in a Payday generator I use.
Create a dedicated table, and a simple loop script to generate a day
record for each day of the required year :

using a global g_Year, similar to:

• Allow User Abort [ On ]
• Freeze Window
• New Record/Request
• #crate initial January 1st date using global YEAR value
• Insert Calculated Result [ G. Paydays::WD_date; Date(1; 1; G.
Paydays::_year) ] [ Select ]
• #grab the current record DATE to global
• Insert Calculated Result [ G. Paydays::_hold; G. Paydays::WD_date ]
[ Select ]
• Loop
• New Record/Request
• Insert Calculated Result [ G. Paydays::WD_date; G. Paydays::_hold +
1 ] [ Select ]
• Insert Calculated Result [ G. Paydays::_hold; G. Paydays::WD_date ]
[ Select ]
• Exit Loop If [ GetAsNumber(G. Paydays::WD_date) = Date(12; 31; G.
Paydays::_year) ]
• End Loop
• Go to Layout [ original layout ]
• Refresh Window
• Exit Script

The Month, and day of year can be easily calculated with calc fields,
from the record Date (DayDate)
DayDate
DoW_number = DayOfWeek ( DayDate )
DoW_name = DayName ( DayDate )
MoD = Month(DayDate)


then all you need is two globals to nominate the day and month you want
to evaluate (g_day, g_Month)
a rel from this to the MoD calc, and DoW_number (FM7, two field for the rel)
and a Count calc using the rel

Even a bit simpler, the calc coul use the selected month and just create
records for it starting at teh first day of te month, rather than the
first day of the year...



Chris Brown
Neurosurgery
University of Adelaide