# 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

• @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!