Bi-Weekly Pay Period update and points added during said pay period.

SamuelK

Hey everyone, I am fairly new to Smartsheet, so I am sorry if this is actually pretty easy. But, I have been trying to find the answer for a hot minute and even played around for hours, so I am going crazy. Thank you for your help in advance.

I would like to do two things. Have the two pay period columns update themselves based off of a two week pay period (14 days). The current one started back on 6/17 and ends 6/30. So I would like it to update to 7/01 to 7/14, once it lands on the appropriate starting date. I'm open to all suggestions, including reducing this down to a single column if you have found it easier. Like I said, I am very new and need all the advice.
And the second thing I would like to do, is have the points from the Total Points Daily column added up and the total placed in to the Total Points Pay Period column, based off the range of those dates. Aka, 6/17 to 6/30.

"=SUMIFS([Total Points Daily]:[Total Points Daily], Employee:Employee, Employee@row, Date:Date, ">=06/17/24", Date:Date, "<=06/30/24")"

I was able to use the above formula to create the numbers per specific employee between the two dates, using just the Date column. I had to convert the date column to a text/number column instead of a date column, which I thought was weird. I would also then have to manually switch the code every pay period and wouldn't be able to convert the formula to a column formula. So I stopped using it.

The current Date column is populated form a drop down menu through a form. Along with the rest of the info.

So, I guess in a nutshell what I am asking for is… Is there a formula that will show the totaled points from the Total Points Daily column between two dates (the pay period), in my Total Points Pay Period column, for the specific employee in that row? And how can I get my Pay Period dates to auto update to the next pay period so that the points reset and begin recounting between the next two dates.

Again, thank you for all of the help in advance.

Spoonhead

I'm not 100% sure if this is what you're saying, however from my interpretation, try this:

You can use a SUMIFS formula to sum points between the pay period start and end dates. To avoid manually updating the dates, we'll use a dynamic approach. You'll need to use what we call in the Smartsheet world 'Helper Columns' (new columns placed on the end of the sheet on the right to help achieve functional requirements!).

Helper Columns Setup:

Add columns "Next Pay Period Start" and "Next Pay Period End" with the formulas provided below. Use the provided SUMIFS formula in your "Total Points Pay Period" column.

Next Pay Period Start: To calculate the next pay period start date.

=IF(TODAY() >= [Pay Period End]@row, [Pay Period Start]@row + 14, [Pay Period Start]@row)

Next Pay Period End: To calculate the next pay period end date.

=IF(TODAY() >= [Pay Period End]@row, [Pay Period End]@row + 14, [Pay Period End]@row)

Points Calculation:

Total Points Pay Period: To calculate total points within the current pay period.

=SUMIFS([Total Points Daily]:[Total Points Daily], [Employee Temp]:[Employee Temp], [Employee Temp]@row, Date:Date, ">=" + [Pay Period Start]@row, Date:Date, "<=" + [Pay Period End]@row)

If you prefer you should be able to use automation:

Create an Automation Rule:

1. Go to the Automation menu in Smartsheet and create a new workflow.
2. Set up a trigger to check for the end date.
3. Set an action to update the pay period start and end dates to their next values.

This setup will allow you to automatically roll over to the next pay period and sum the points accordingly.

See if that meets your needs. Let me know!

SamuelK

Hey Spoonhead, thanks for replying! I didn't see a reply directly option on the site. So hopefully you see this. But I am now getting only 0 for in the Total Points Pay Period column.

dojones

Try this

=SUMIFS([Total Points Daily]:[Total Points Daily], Employee:Employee, Employee@row, [Pay Period Start]:[Pay Period Start], >=[Pay Period Start]@row, [Pay Period End]:[Pay Period End], >=[Pay Period End]@row)

Also make sure all dates are date fields.