Move select column data to new row

Tracy Huang

I have a Smartsheet form that allows employees to request up to 5 unique dates and times (to come on campus). For these dates and times, I have columns: Date1, Time1, Date2, Time2, etc.

Ideally, I'd like to have one column of date and one column of time so that I can pull a report on a daily basis for our access point security officers.

To accomplish this, I have a workflow that copies any new request/row to a new Sheet. The number of rows created is based on number of dates entered. In the new Sheet, I have two new column-- Access Date and Access Time. However, I am not sure how to move the cell Date2 to second record for the same request, and Date3 to the third record, etc.

I have tried creating unique ID to identify the row and DataMesh, but I am missing the "instruction" to direct Date2 to a specific cell. I think Joint(collect) formula might be helpful, but I am not sure where to start with it.

Any suggestions would be appreciated. Thank you.

Tracy Huang

After some additional trial and error, instead of trying to create a complicated formula, I came up with this solution:

1. I created additional 5 Smartsheets (Smartsheet1, Smartsheet2, Smartsheet3, Smartsheet4 and Smartsheet 5) each to correspond to the Date and Time fields in the original Smartsheet Form.
2. After form is submitted and approved, Automation copies each row to up to 5 different Smartsheets (depending on how many dates are entered). If Date 1 is requested, then row is copied to Smartsheet 1. If Date 2 is requested, then row is copied to Smartsheet 2, etc.
3. In each of the Smartsheet1, Smartsheet2.., I have 2 new columns of Access Date and Access Time with formula to copy the corresponding Date and Time into the new columns. For example, for Smartsheet2, I copied Date 2 and Time 2. The formula I used: =IF([Request Date 2]@row > TODAY() - 1, [Request Date 2]@row)
4. A report (or I could have made this a new sheet) then combines all 5 Smartsheets with five dates stacked in one column.

Thank you for the community. I figured out this solution based on different solutions offered for different pieces of the puzzle.

Tracy Huang

After some additional trial and error, instead of trying to create a complicated formula, I came up with this solution:

1. I created additional 5 Smartsheets (Smartsheet1, Smartsheet2, Smartsheet3, Smartsheet4 and Smartsheet 5) each to correspond to the Date and Time fields in the original Smartsheet Form.
2. After form is submitted and approved, Automation copies each row to up to 5 different Smartsheets (depending on how many dates are entered). If Date 1 is requested, then row is copied to Smartsheet 1. If Date 2 is requested, then row is copied to Smartsheet 2, etc.
3. In each of the Smartsheet1, Smartsheet2.., I have 2 new columns of Access Date and Access Time with formula to copy the corresponding Date and Time into the new columns. For example, for Smartsheet2, I copied Date 2 and Time 2. The formula I used: =IF([Request Date 2]@row > TODAY() - 1, [Request Date 2]@row)
4. A report (or I could have made this a new sheet) then combines all 5 Smartsheets with five dates stacked in one column.

Thank you for the community. I figured out this solution based on different solutions offered for different pieces of the puzzle.

Genevieve P.

Hi Tracy,

Thank you for posting your solution! I'm sure this will help others in a similar situation.