Formula to push only certain rows to a report

Nicole Arnold
Nicole Arnold ✭✭
edited 06/21/22 in Formulas and Functions

Hello! We had contracted with Smartsheet last year to help build out a timesheet. We didn't end up implementing that version for a number of reasons, but now that we've had a a few changes for our non-exempt team pay dates, we are ready to. When Smartsheet helped us build the sheet and report, the timesheet was due 2 days after the end of the pay period and supervisor approval was required. Now the timesheet is due 1 day after the end of the pay period, and the supervisor has been removed from the process. I updated the due dates and removed the supervisor. The original report associated with the timesheet was set to push the current pay period dates to the report, and at the end of the pay period, 2 or 3 days would also push to the report for the overlap between end of pay period and when the timesheet was due. For the current PE 6/30 pay period, the report is already pushing 7/1 into the report (as of 6/20). I can't figure out quite why as the formulas are very complicated to me and we had to do quite a bit of customization considering we are not on the enterprise plan. We would like the report to show only the current pay period rows, with the exception at the end of the pay period in which the report would show 2 days into the new pay period. Hopefully this makes sense. I've published both the sheet and report for editing and am hoping someone can help with the formulas.


Report - https://app.smartsheet.com/b/publish?EQBCT=bd319ff6b7584bfe89c28e62cf13cec2

Sheet - https://app.smartsheet.com/b/publish?EQBCT=c5e34fa4eac045ddb52504d7cc063558

Answers

  • After reviewing today, additional days have not been pushed to the report, so it looks like perhaps the sheet is pushing the full pay period plus the day the sheet is due only. Will keep monitoring but if this is the case, this will work just fine for us for now.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Nicole Arnold

    I hope you're well and safe!

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Hi @Andrée Starå . Thanks so much for replying! I had shared the links to the sheet and report, which were published for full access, but did also just share the individual items with you. We'd like employees to be able to use the report vs the sheet since the sheet can be very overwhelming, especially for employees who do not work in Smartsheet often (the report pulls just the current pay period rows). Our timesheets are due one day after the end of the pay period. Because we don't use a typical clock in/clock out system, employees are not great at inputting their time daily. For this reason along with the fact that the timesheet is due 1 day after the end of the pay period, we need the rows for the applicable pay period to be visible on the report through the timesheet due date. Right now, the report is showing the full pay period which ends 6/30, and is also showing July 1. When the SS team completed the project for me last year, I'm certain that July 1 would not have shown up until July 1. If there is an easy fix, that would be great, but it seems July 1 is the only date that is pulling with the current pay period, and if that stays true, we are ok using as-is. I just don't want multiple days for the following pay period showing up on the report. I hope that makes sense and helps.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Nicole Arnold

    Thanks!

    Ok, let me know if it doesn't work as expected.

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Nicole Arnold

    Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • @Andrea Zenner My question was not resolved and is why I did not accept the answer/mark it helpful. If you have any time to look at the timesheet, a new problem has come up - it's currently pushing period ending 7/31 to the report when it should be pushing period ending 7/15. I accidentally deleted the sheet and report I shared with you previously and just shared a new copy of the template with you. Any assistance you can provide would be greatly appreciated.

  • Andrea Zenner
    Andrea Zenner ✭✭✭✭✭

    @Nicole Arnold did you mean for your comment to be directed to @Andrée Starå ?

    Andrea Zenner

    Program Manager | Infrastructure & Operations

    Apogee Enterprises

    EAP | Mobilizer | Core Product Certified | Superstar

    azenner@apog.com

  • Yes @Andrea Zenner ! My apologies! @Andrée Starå Please see my last message and let me know if you can assist.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!