How to count hours between punch in and punch out

✭✭✭✭

How can I count hours worked in the example below?

Thanks,

Ron

• ✭✭✭✭✭

=IFERROR(IF(((VALUE(LEFT([Time Out]@row, 2)) + VALUE(RIGHT([Time Out]@row, 2)) / 60) - (VALUE(LEFT([Time In]@row, 2)) + VALUE(RIGHT([Time In]@row, 2)) / 60)) > 0, ((VALUE(LEFT([Time Out]@row, 2)) + VALUE(RIGHT([Time Out]@row, 2)) / 60) - (VALUE(LEFT([Time In]@row, 2)) + VALUE(RIGHT([Time In]@row, 2)) / 60)), ((VALUE(LEFT([Time Out]@row, 2)) + VALUE(RIGHT([Time Out]@row, 2)) / 60) - (VALUE(LEFT([Time In]@row, 2)) + VALUE(RIGHT([Time In]@row, 2)) / 60)) + 24), "")

• ✭✭✭✭✭

Turns out your extra constraints simplified the formula.

=IFERROR((((VALUE(LEFT([Time Out]@row, 2)) + VALUE(RIGHT([Time Out]@row, 2)) / 60) - (VALUE(LEFT([Time In]@row, 2)) + VALUE(RIGHT([Time In]@row, 2)) / 60)) + (([Date out]@row - [Date In]@row) * 24)), "")

• ✭✭✭✭✭✭

I hope you're well and safe!

Try something like this.

=IF(OR([Time In]@row = "", [Time Out]@row = ""), "", IFERROR((((VALUE(LEFT([Time Out]@row, 2)) + VALUE(RIGHT([Time Out]@row, 2)) / 60) - (VALUE(LEFT([Time In]@row, 2)) + VALUE(RIGHT([Time In]@row, 2)) / 60)) + (([Date Out]@row - [Date In]@row) * 24)), ""))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭

=IFERROR(IF(((VALUE(LEFT([Time Out]@row, 2)) + VALUE(RIGHT([Time Out]@row, 2)) / 60) - (VALUE(LEFT([Time In]@row, 2)) + VALUE(RIGHT([Time In]@row, 2)) / 60)) > 0, ((VALUE(LEFT([Time Out]@row, 2)) + VALUE(RIGHT([Time Out]@row, 2)) / 60) - (VALUE(LEFT([Time In]@row, 2)) + VALUE(RIGHT([Time In]@row, 2)) / 60)), ((VALUE(LEFT([Time Out]@row, 2)) + VALUE(RIGHT([Time Out]@row, 2)) / 60) - (VALUE(LEFT([Time In]@row, 2)) + VALUE(RIGHT([Time In]@row, 2)) / 60)) + 24), "")

• ✭✭✭✭

Thank you Devin. Works perfectly.

• ✭✭✭✭

I just realized that it is not correct if punch out is more that 24 hours after punch in.

• ✭✭✭✭

How would this be modified to account for punchs that span past 24 hours or multiple days?

• ✭✭✭

Hi, just an idea but you could do an if then formula if the value was over X it flags it to you or whomever has acces to get the person to correct their clocked in time...?

• ✭✭✭✭

@GinaP I need to track contractor hours that respond to snow events that sometimes last over 24 hours or even multiple days.

Regards,

Ron

• ✭✭✭
edited 02/19/23

@Ronald Anderson is there a system of record they are logging into or you are just provided a time in and time out? Could you create a form from a SS that fed to your tracker to provide them with a column that indicated actual hours worked. The form would then auto populate a cell linked to their name which could also include the time in and out of the project?

• ✭✭✭✭

@GinaP no, it is a simple Smartsheet form that is filled out after a snow event by the manager.

• ✭✭✭✭✭

Turns out your extra constraints simplified the formula.

=IFERROR((((VALUE(LEFT([Time Out]@row, 2)) + VALUE(RIGHT([Time Out]@row, 2)) / 60) - (VALUE(LEFT([Time In]@row, 2)) + VALUE(RIGHT([Time In]@row, 2)) / 60)) + (([Date out]@row - [Date In]@row) * 24)), "")

• ✭✭✭✭

@Devin Lee thank you for your help. This is what I was looking for.

Regards,

Ron

• ✭✭✭✭

@Devin Lee Is it possible to edit your formula to leave the cell blank or make the value 0 if the cells in Time In or Time Out is blank?

Regards,

Ron

• ✭✭✭✭✭✭

I hope you're well and safe!

Try something like this.

=IF(OR([Time In]@row = "", [Time Out]@row = ""), "", IFERROR((((VALUE(LEFT([Time Out]@row, 2)) + VALUE(RIGHT([Time Out]@row, 2)) / 60) - (VALUE(LEFT([Time In]@row, 2)) + VALUE(RIGHT([Time In]@row, 2)) / 60)) + (([Date Out]@row - [Date In]@row) * 24)), ""))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭

I works perfectly. Thank you so much for your help!

Regards,

Ron

• ✭✭✭✭✭✭

Excellent!

Happy to help!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!