FileMaker TIP: Current Quarter Date Range

I do a lot of applications where users want reports or lists that take a date in the current record and filter or find all the records "In This Quarter".

The calculation isn't all that difficult - but it's a cool time saver. You can make a script that returns the result, or make a custom function.

Note that in this formula, I've created local variables - so you can use the $quarterStart and $quarterEnd dates in other places in your script.


Let ([
   $inDate = theDate; //can be passed as parameter
   $mo = Month($inDate);
   $yr = Year($inDate);


   $qStart = If ( $mo > 9 ; Date (10;1;$yr);
                 If ( $mo > 6 ; Date (7;1;$yr);
                 If ($mo > 3 ; Date (4;1;$yr);
                 Date(1;1;$yr)
               )));
   $qEnd = If ( $mo > 9 ; Date (12;31;$yr);
                 If ( $mo > 6 ; Date (9;30;$yr);
                 If ( $mo > 3 ; Date (6;30;$yr);
                 Date(3;31;$yr)
              )))
   ];
   $quarterStart & "..." & $quarterEnd
)