Sum hours by pay period

  • 0
  • 1
  • Question
  • Updated 1 week ago
  • Answered
Trying to sum time cards by pay period. I have one table to collect hours worked.
Table is "Time Cards". Contains [Date], [Job], [Employee], [Hours].
Have also built a Pay Periods table for 2019 that contains [Pay Period], [Beginning Date], [Ending Date], [Pay Date]
Pay periods are every two weeks starting on 12/31/18.
Pay Periods has a one to many relation to Time Cards.
At the end of my pay period, I need to add up the hours worked by employee for that pay period.
I can make a simple report against the Time Cards by date with a filter that prompts for beginning date and ending date, but the leads to potential errors when selecting the correct date range, so I would like to either attach the appropriate pay period number to a time card record and then group by that, or run a report on pay periods and group by the period.
The part I'm stuck on is assigning a date to the pay period range, unless my thinking is wrong and there's a better approach? I previously did this in Excel via a VLOOKUP formula.
Photo of Brian Gardner

Brian Gardner

  • 120 Points 100 badge 2x thumb

Posted 3 weeks ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
no problem.  FirstDayOfPeriod function to the rescue.  It was really designed for your exact use case, like a very typical 2 week pay cycle than never changes come hell or high water.

Change the Key field of your pay periods table to be the field [Beginning Date]

The on the details time entries make a formula field for Pay Period beginning date

https://login.quickbase.com/db/6ewwzuuj?a=dr&r=bm&rl=w5j

FirstDayOfPeriod([date field], Weeks(2), Date(2018,12,31))

Use that field as the reference field on the left side if the relationship were 1 pay period record has many time entries and then make your summary field.
Photo of Brian Gardner

Brian Gardner

  • 120 Points 100 badge 2x thumb
That works very nicely, thank you.
Taking this one step further, on my time card form where I now see the pay period drop down box, it would be great if the value of the pay period defaulted to the correct period for the date of the time card. In other words, when I create a new time card entry and select the date I worked, have the pay period set to the correct period for that date. I want to say this should be a filter or dynamic form rule, but I can't quite make the connection.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
Ok, try this

Make a formual date field called first day of current time period.

The formula will be firstdayofperiod(today(), weeks(2), Date(2018,12,31))

Then have a form rule to fire that says when the pay period date is blank change pay period blank to gr value in the field first day of current time period.

There is a chance that you will need to put that field on the bottom of the form to get that form rule to fire. Also set that checkob on t(e form rule to unchecked, to be sure it fires.
Photo of Brian Gardner

Brian Gardner

  • 120 Points 100 badge 2x thumb
I don't know if I got myself worked around in knots on this, but I seem to have it working...not exactly as you described but I am able to confidently enter time and report on it for payroll purposes.

I very much appreciate your help on this.
Brian
Photo of Damon Richardson

Damon Richardson

  • 100 Points 100 badge 2x thumb
Hi, this is my first time responding to the community, but I had the exact same problem and resolved it using the automation feature.

Essentially, when someone enters a time card entry, the automation is triggered based on (adding or modifying) the "Date Worked" field in the Time Card table. After the date is entered, the automation has Quick Base to see if the "Date Worked" value falls between one of the (Payroll Calendar Table) "Beginning Date" and "End Date" values, each pair has an associated Payroll Period.

Once QB determines which record ID# row (where the Date Worked fell between a begin and end date on payroll calendar), it copies the Payroll Period (and any other associated fields) to a third, many-to-many table that is linked to both the Payroll and Time Card tables.

If you still are looking for a solution, I can provide screen shot details of the automation, relationships and tables.

Photo of Brian Gardner

Brian Gardner

  • 120 Points 100 badge 2x thumb
That sounds very interesting. What I ended up doing works, but still leaves open the possibility of choosing the wrong period.