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

Joevania Alexandre

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), ", ")

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/b/publish?EQBCT=6eb7ce1439f54a919e123e11036306fb

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"

Paul Newcome

Can you provide a sample of the expected final outcome?

Joevania Alexandre

Hi @Paul Newcome ,

Here's an example of what the final timesheet could look like for each person (we're open to ideas/changes as long as we can capture the data). Ideally, each day worked would go on one line. However, Document Builder generates one document per row. So as a workaround, I'm trying to combine all the data for each pay period into one row. I also need to be able to sum up the total hours for each of the 4 types of activity codes. Lastly, I need to use the DocuSign integration to get signatures.

Paul Newcome

Sorry. I meant manually entered data into the first screenshot that shows what you would expect the final outcome to look like there.

Joevania Alexandre

Hi @Paul Newcome ,

Here you go! We collect the data via a form. These are submissions from different employees. I've hidden the name field for privacy.

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/b/publish?EQBCT=6eb7ce1439f54a919e123e11036306fb

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"

Paul Newcome

@Joevania Alexandre I understand that is how you are collecting the information, but how do you want it in the consolidation sheet? Your very first screenshot?

Joevania Alexandre

@Paul Newcome

Let's see if I can do a better job of explaining the process. Step 2 is where I need assistance.

Step 1: The employees submit the time form.

Step 2: The data is consolidated in this format.

The column I've circled is where I especially need help with the Join/Collect function (or alternative) and the total hours per category.

I'd prefer to have the dates listed in chronological order and a line between each if possible. Some days may have 2 shifts such as the example for person 2.

Step 3: Document Builder generates a report per person per reporting period that is integrated with DocuSign for signature.

I hope this is more clear. Thanks for trying to help :-)

Paul Newcome

That's how I thought you were going for but wanted to confirm. You will need a helper column on the form sheet that creates that string on every row using a formula.

Then in the consolidation sheet you would use the JOIN/COLLECT but you would pull in from the helper column just created on the form sheet.

The delimiter for the JOIN/COLLECT will be CHAR(10) + CHAR(10)

=JOIN(COLLECT(.....................), CHAR(10) + CHAR(10))

CHAR(10) is a line break, so adding them together gives you a double line break.

Joevania Alexandre

Thank you so much @jmyzk_cloudsmart_jp! That's exactly what I needed! Some have two shifts. When I combine, I'm now getting some that have "0" hours for the second shift. How do I disregard those?

Joevania Alexandre

Thank you also @Paul Newcome! I appreciate your assistance.