Calculating net hours

I've found Paul Newcome's mega post on calculating hours and from that have successfully created the total number of hours worked. But I am trying to have it calculate net hours worked after taking away any unpaid break. Breaks will be 30 mins or 60 mins. I've seen other posts from people asking about it but haven't seen any solutions. I've tried various solutions presented by AI and nothing is working. Below is screenshots of the 2 formulas that are working perfectly but now I need it to calculate hours minus the break.

I'm want to get the sheet to a point where employee enters in Start time, finish time, break taken and the rest is worked out. Shifts are always completed on the same day.

1st screenshot formula -

=((VALUE(LEFT([End Time]@row , FIND(":", [End Time]@row ) - 1)) + VALUE(RIGHT([End Time]@row , 2)) / 60) + ([End date]@row - [Start date]@row ) * 24) - (VALUE(LEFT([Start Time]@row , FIND(":", [Start Time]@row ) - 1)) + VALUE(RIGHT([Start Time]@row , 2)) / 60)

2nd screenshot formula -

=INT(SUM@row ) + ":" + IF(((SUM@row - INT(SUM@row )) * 60) < 10, "0") + ((SUM@row - INT(SUM@row )) * 60)

Find more posts tagged with

Formulas

Time Calculation

Accepted answers

Paul Newcome

Give this a try in the SUM column:

=((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End date]@row - [Start date]@row) * 24) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2)) / 60) - ([Unpaid Break (mins)]@row / 60)

All comments

Philip Robbins

@Josephine am I missing something? Feels like you would just need to subtract 1 (for 60 mins) or 0.5 (for 30) from the overall result to get the net minus the break.

Paul Newcome

Give this a try in the SUM column:

=((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End date]@row - [Start date]@row) * 24) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2)) / 60) - ([Unpaid Break (mins)]@row / 60)

Josephine

Thank you @Paul Newcome - that did it 😄

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.