by Michael Smith, via <editor@filemakermagazine.com>
First Monday
BONUS: FIRSTMON.FP3
PLATFORM: Mac/Win
Sure sounds simple when you say it that way, "I'll just bill you on the first Monday of the month." When exactly is the first Monday of any given month? In this article we'll explore some of the techniques and required information needed to successfully write this and other Date calculations.
Getting Started
The first thing is to have a good description of exactly what you want. In this case, the name of this article pretty much states it as simply as anyone could. But to be specific, we want the date of the first occurring Monday for the given month and year. Hmm... starting to sound a little like an algebraic word problem? I hate word problems, especially when after I have struggled through my calculation dilemma, I realize that all of the clues on building this calculation were glaringly listed in the statement itself. Here is the list of landmarks we'll be dealing with along the way:
date
first occurring Monday
month
year
Date Function Follies
Date functions are kind of awkward for most because you can easily fall victim to the wrong result type, either directly by choosing one over the other or more tragically when poorly evaluating a function's capability by assuming the wrong result type. If you find that you write too few date calcs to remain up on the slight caveats, it's worth a look through the help index to rid your mind of any misconceptions. Lets take a look at the functions we'll be using before we assemble the calculation:
Date (date) -Returns a date
Month (date) -Returns a number
Year (date) -Returns a number
DayOfWeek (date) -Returns a number that represents the day of the week. Sunday is 1, Monday is 2 and so on.
Where to Start
Sometimes it's difficult to even figure out where to begin. My algebra teacher in high school used to always say to turn a larger problem into several smaller ones I could solve and this advice still serves me well. So where are the smaller problems in this puzzle? Let's take a look.
We want the date of the first occurring Monday for the given month and year.
Since the first Monday will either be the first, second, third etc. through the seventh day of the month, we'll need to first know the the first day of the month as a starting point before we determine where the first Monday is. Consider:
Date(Month(Date),1,Year(Date))
For a date entered as 07/12/98, the calculation will return 07/01/98 because the parameters of the date function specify the Month of the entered date, 1 as the day value and the year of the entered date.
Where To From Here?
Now that we have the first day of the month, we need to know what day that is. If I told you that the first day of the month was a Wednesday, you could recite through the days, counting how many you listed and adding that to the first day of the month to determine the date of the first Monday. If you didn't know what day the first day of the month was, you wouldn't have enough information to return a correct answer. Here is that calculation, it builds on the first calc we wrote.
DayofWeek(Date(Month(Date),1,Year(Date)))
This calculation for the given date of 07/12/98 will return a 4. Remember that the legend for DayOfWeek starts with Sunday = 1 on through Saturday = 7, so the first day of this given month is Wednesday.
How Many Days to Add
Counting from Wednesday through the weekend and on to the first Monday is easy for us, we do it all of the time. We recite the order of the days of the week without even noticing. How can we get FileMaker to do the same? Well, first of all, we're talking computers here. The are elaborate calculators that thrive on math so we'll have to work in its familiar ground. Remembering that the days of the week are handled by a numbering sequence that starts with 1 for Sunday, and cycles on the number 7, we'll look for the pattern and teach it to FileMaker. Considering that Wednesday is 4, Monday is 5 days away. If the first day was Tuesday which is 3, Monday would be 6 days away. Notice that pattern here? 4 + 5 = 9 and 3 + 6 = 9 and so on. Why is 9 such a magical number? Well if you add the number of positions Monday is away from the number of the starting day, the sum is always 9.
We're not in the clear yet though. Considering the if the first day of the month were 2 which is a Monday, then yes, if we counted 7 places to the next Monday, the sum of the to would again be that magical 9 but the result would place us at the second Monday rather than the first. So we can see two situation that complicate our direction. If the first day of the month were a Sunday or a Monday, we risk missing the first Monday altogether. The Function that can sort this out for us is the Mod function.
The Mod Function
The Mod (Modulo) function returns the remainder of a division. This can assist us by telling us how much to add to the day of week number without wrapping around to the second Monday. Consider the following formula:
Mod(9-DayofWeek(Date(Month(Date),1,Year(Date))),7)
Here we are subtracting the DayOfWeek number from 9 and then dividing that result by 7 and returning the remainder. Sounds a little daunting so lets plug some numbers into that statement and see how it works. If the DayOfWeek is 3 then the 9 - 3 = 6. Now we divide this number by seven and because seven doesn't go into 6, the remainder is 6. 6 + 3 = 9. Lets see how that works with 1. 9 - 1 = 8 and that divided by seven has a remainder of 1. 1 + 1 = 2 which is a Monday. The last situation to consider is if the first day of the month is already Monday. 9 - 2 = 7 and that divided by seven has a remainder of 0. 2 + 0 = 2 which is a Monday.
The Whole Enchilada
Now that we know where to start, lets look at the whole calculation.
Date(Month(Date),1,Year(Date))+
Mod (9-DayofWeek(Date(Month(Date),1,Year(Date))),7)
The first part of the calculation before the "+" symbol gets us started on the first day of the month. The second part of the equation which is added to the first evaluates how many days we have to add to the starting date to arrive at the first Monday.
## END ##