How to count hours between punch in and punch out

Ronald Anderson

How can I count hours worked in the example below?

Thanks,

Ron

Devin Lee

=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), "")

Devin Lee

@Ronald Anderson

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)), "")

Andrée Starå

Hi @Ronald Anderson

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!

Devin Lee

=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), "")

Ronald Anderson

Thank you Devin. Works perfectly.

Ronald Anderson

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

Ronald Anderson

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

GinaP

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...?

Ronald Anderson

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

Regards,

Ron

GinaP

@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?

Ronald Anderson

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

Devin Lee

@Ronald Anderson

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)), "")

Ronald Anderson

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

Regards,

Ron

Ronald Anderson

@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

Andrée Starå

Hi @Ronald Anderson

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!

Ronald Anderson

Hi @Andrée Starå ,

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

Regards,

Ron

Andrée Starå

@Ronald Anderson

Excellent!

Happy to help!