Date calculation

[sunkat]

Hi, I am completely new to Filemaker. I work on a database for timesheets to calculate hours worked by employee to calculate their wages. But the wages are paid not per calendar month, but a payroll month. Payroll month starts on a Sunday before last Thursday of the previous month and ends on a Saturday before last Thursday of the month.

How do I calculate a payroll month for a timesheet date?

Thanks

[comment]

This should get you started: the date of the Sunday before last Thursday of the previous month for any given date can be calculated as =

Let (
firstOfMonth = Date ( Month (  GivenDate ) ; 1 ; Year ( GivenDate ) )
;
firstOfMonth + Mod ( 5 - DayofWeek ( firstOfMonth ) ; 7 ) - 11
)

[Lee Smith]

Be sure that the result of the calculation is Date.

[comment]

On second thought, to simplify the matter one should calculate the date of the Sunday before last Thursday of this month instead. Then, if the given date is before that date, it belong to this month - otherwise it belongs to the next month. So cPayrollMonth =

Let ( [
firstOfNextMonth = Date ( Month (  GivenDate ) + 1 ; 1 ; Year ( GivenDate ) ) ;
lastSun = firstOfNextMonth + Mod ( 5 - DayofWeek ( firstOfNextMonth ) ; 7 ) - 11
] ;
Case (
GivenDate < lastSun ; Date ( Month (  GivenDate )  ; 1 ; Year ( GivenDate ) ) ;
firstOfNextMonth
)
)

This returns the date of the 1st of the month to which the given date belongs. This type of result is most convenient for sorting and grouping - and you can format it to display as May 2014, for example, without affecting the sort.

[sunkat]

Comment, thanks, that worked.