Combine shifts (dates worked/clock in/clock out/ activity type for specified pay period & person)


Hello Smartsheet Community,

Has anyone successfully used Join/Collect to combine data from multiple columns? I have a sheet with the time submission/shifts worked by employees and another sheet for combining the data. I need to collect all of the approved shifts for a specified pay period for each specified person, so each person can have one row with all their shifts (start, end time, total hours worked, category of work) for each pay period. I'm hoping to use Document Builder to automatically generate a PDF document to send to DocuSign with their respective shifts and total hours worked from a list of categories of activities. I've successfully combined all dates worked for a pay period but can't seem to add the additional categories (such as shift start time, end time, hours worked, category, and only if they have been approved) and filter by individual employee.

Any ideas or thoughts on how I can get all this data in one row to be able to use document builder?

So far the dates worked formula that pulled in the dates from a specified pay period was =JOIN(COLLECT({Dates Worked}, {Reporting Period Time Submission}, [Current Pay Period]@row), ", ")

Best Answer

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓

    Hi @Joevania Alexandre

    Instead of "Join/Collect to combine data from multiple columns," I would combine data from multiple columns to a single cell with a formula and join the cells with "Join/Collect."😁

    Below is a demo solution based on your description. You can check the created documents from a row attachment.

    Here are the formulas for each column;

    • total hours worked
      • =SUMIFS({total hours worked}, {Employee Name}, [Employee Name]@row, {Week #}, [Week #]@row, {Approval}, 1)
    • All Approved Shift Data
      •  =JOIN(COLLECT({Combined Shift Data}, {Employee Name}, [Employee Name]@row, {Week #}, [Week #]@row, {Approval}, 1), CHAR(10))
    • Category 1
      • =JOIN(COLLECT({Combined Shift Data}, {Employee Name}, [Employee Name]@row, {Week #}, [Week #]@row, {Approval}, 1, {Category}, "Category 1"), CHAR(10))
    • Combined Shift Data
      • =Date@row + ", " + [Start Time]@row + " - " + [End TIme]@row + ", " + [total hours worked]@row + " Hours"


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!