Today’s vexing problem in FileMaker was in calculating bi-weekly intervals (as in a payroll period) without using a lookup table. For example, given a payroll period of:
| Start | End |
| 11 Jan 2009 | 24 Jan 2009 |
| 25 Jan 2009 | 7 Feb 2009 |
| 8 Feb 2009 | 21 Feb 2009 |
| 22 Feb 2009 | 7 Mar 2009 |
I wanted to be able to calculate the last and current pay period for any given date. After some digging around FMForums and more trial-and-error on my part, the formulas are:
To find the last pay period:
START = 14 * Div ( givenDate - 14 ; 14 ) END = 14 * Div ( givenDate - 14 ; 14 ) + 13
And to find the current pay period:
START = 14 * Div ( givenDate ) ; 14 ) END = 14 * Div ( givenDate ) ; 14 ) + 13