Often we are asked to produce reports that show total sales (or student attendance, or whatnot) by week. In order to produce these reports, we need some value that allows us to group our records by week. What we can use is the Sunday—the start of our week—that occurs before our activity date.
For example, let’s say we had sales records for the month of January, 2010. January started on a Friday; if we take 1/1/2010, we can “reduce” it to the preceding Sunday—12/27/2009. Similarly, Saturday the 2nd also falls in the week beginning 12/27/2009. On Sunday we start a new week: January 3rd through the 9th all fall in the week starting 1/3/2010. Once we have found the date for the start of the week for each sale, we can group our sales by that date and produce our report.
The formula for finding the preceding Sunday for a date (unless said date is a Sunday, in which case we just want that date) is very simple. Dates can be treated as numbers, and Sundays happen to be evenly divisible by seven. Basically, we find the most recent date before our given date that is evenly divisible by seven by dividing said date by seven, throwing away any remainder, and multiplying back by seven:
Div ( date ; 7 ) * 7
But what if you want to run your weeks from Monday to Sunday, and need to find the preceding Monday instead? You can’t just add one to the preceding Sunday, because you’d get weird results when activity fell on a Sunday (simply adding one to the above formula gives you the following Monday when date is a Sunday). You need to do a little “shifting” first to your original date, subtracting the offset between Sunday and Monday, finding the preceding Sunday for this earlier date, and then adding back the offset to find the Monday to start your week.
Div ( date - 1 ; 7 ) * 7 + 1
Similarly, if you want to start your week on Tuesday for some reason, you just use an offset of 2:
Div ( date - 2 ; 7 ) * 7 + 2
Finding the start of the week for dates is useful for more than just summary reports. If you’ve ever wanted to create a calendar interface in FileMaker, you’ll use similar formulas along the way. In fact, I may have something to say about calendars and FileMaker Pro 11 soon…