Find Next Timestamp Below current row based on Employee Name.

Christian Graf
Christian Graf ✭✭✭✭✭
edited 07/02/21 in Smartsheet Basics

I have a list of time entries that are populated when barcodes are scanned.

ID 1 - Bill - 06/15/21 1:59PM - Part1 - new cell(6/15/21 3:00PM)

ID 2 - John - 06/15/21 2:35PM - Part2 - new cell(No Entry Yet)

ID 3 - Bill - 06/15/21 3:00PM - Part 3 - new cell(06/15/21 4:30PM)

ID 4 - Bill - 06/15/21 4:30PM - Part 4 - new cell(No Entry Yet)


I'm trying to populate a new cell the employee's next timestamp based on Employee Name. So I need a formula to look below the current entry for the first entry that matches the employee name, and grab the timestamp from the entry it found to populate a end time in the new cell.

Here's what I tried, I'm sure I'm not even close:

=IF([Start Time]@row = MIN(COLLECT([Start Time]:[Start Time], [Employee Name]:[Employee Name], =[Employee Name]@row,[Time Log ID]:[Time Log ID], [Time Log ID]@row>1)[Start Time]:[Start Time]),[Start Time (H+M)]MATCH(),0)


Thanks

Best Answer

  • Christian Graf
    Christian Graf ✭✭✭✭✭
    Answer ✓

    I have no clue what I did, but it works now.


    =IF([Start Time (H+M)]@row > 0, MIN(COLLECT([Start Time (H+M)]:[Start Time (H+M)], [Employee Name]:[Employee Name], =[Employee Name]@row, [Time Log ID]:[Time Log ID], MATCH([Employee Name]@row, [Employee Name]:[Employee Name]) > [Time Log ID]@row)))

Answers

  • Christian Graf
    Christian Graf ✭✭✭✭✭
    Answer ✓

    I have no clue what I did, but it works now.


    =IF([Start Time (H+M)]@row > 0, MIN(COLLECT([Start Time (H+M)]:[Start Time (H+M)], [Employee Name]:[Employee Name], =[Employee Name]@row, [Time Log ID]:[Time Log ID], MATCH([Employee Name]@row, [Employee Name]:[Employee Name]) > [Time Log ID]@row)))