How to dynamically sum two different rows, same shet.

Darrell42

I can't view my post so i'm posting again sorry smartsheet's!…anyways, HELLO INTERWEBS HUMANS!!!, thank you for taking the time to read my word salad.

smartsheet3.png

What i'm creating: Time tracking workspace for around 60 employees. Has a lander page that accepts form submissions (clock-in /clock-out) (separate rows). User accepts weekly entries based on modified timestamp cell that's matched with users entire, it's just a backup measure to see if the user is truthful. Please view the attachment "smartsheets 3" for this to make sense… I want to sum the clock-in and clock-out "modified column" to get a total for that day (represented in blue and yellow). (**Please don't mind the modified times are booth am or vise-versa you will get the point).

Considerations: 1. Multiple entries from a single employee a day. 2. Not everyone clocks in at the same time. 3. Before accepting submissions, I will be sorting out around 60 a week (Sort - Time Stamps - Ascending). 4. Strictly using the 12 hr format as the form I send to my employees they will us the 12 hr format.

Limitations: 1. Forms cannot be pre filled with info, nor can they append rows. 2. Employees are limited to form only. 3. I cannot trust an employee to fill out a total hours when they clock out, defeats my modified time stamp, for accuracy.

Thank you for reading

=Chris Palmer

Hello @Darrell42

We can explore a method by creating a helper column, titled "Duration"

Use this formula to calculate the Duration:
=IF(AND(NOT(ISBLANK([Clock-In]@row)), NOT(ISBLANK([Clock-Out]@row))),
(NET([Clock-Out]@row - [Clock-In]@row)) * 24, 0)

Then use this formula in the "Total Hours" column:
=SUMIFS([Duration:Duration],[Employee Name:Employee Name], [Employee Name]@row,
DATEONLY([Time Stamp]@row), DATEONLY([Time Stamp:Time Stamp]))

Test the formulas with the following example:
When you input Clock-In as 6:30 AM and Clock-Out as 4:30 PM,
Verify Smartsheet can interpret and calculate the difference (10 hours in this example).

Let me know if this works :-)

Darrell42

Hello Chris! Thank you for helping me but its a little off target in my OP, I stated "a lander page that accepts form submissions (clock-in /clock-out) (separate rows)". If you reference the attachment "smartsheet 3" you will see that blue and yellow highlighted, the blank cell is where the sum would go. Thats what I want the arithmetic to run on, because that is un-mutable and will always be truthful, rather than employees clocking in sadly. As my forms get ingested by the time clock lander there will be (2) rows created for that day at least, maybe even a multiple as techs travel to multiple jobs.

soo…

row 1 <> david | clock in1 time
row 2 <> david | clock out 1 time [take booth these and give me the duration of work that day using the "modified"

Thank you Chris, Im going to mess around with the formulas you gave me and see if I can cook something up.