Jump to content

This topic is 3612 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

Link to comment
Share on other sites

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
)
  • Like 1
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

This topic is 3612 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.