Calculating Working Hours – same day with lunch break

…this post was published on 2016 March 31.

What if we need to calculate working hours in a day, minus lunch time? This request it’s also present on MS Forum.

Let’s suppose that our list contain the following columns, DateTime type columns:

First remark, using validation at column level you should force users to insert values for same day.
The second one , if the user will fill data using direct method  – Edit, some error could occur in time calculation.

What If  Lunch time is  empty?
This mean the user don’t fill JobPause or JobResume. BUT, Sharepoint for empty date columns fill them with year 1899, but it will hide them.
So we need to check if year for JobPause OR JobResume is equal to 1899

If the condition is TRUE, the formula is quite simple to show hours and minutes

If it isn’t we need to calculate minutes betweeb JobStop and JobStart and then substract the lunch break JobResumeJobPause

Starting from this we need to calculate complete hours as integer from minutes divided by 60
and concatenate with “;” and remaining minutes in non-complete hours

Because the result is a text, wee need also to add a zero digit if minutes are less then 10

So, the complete formula is this one:

This entry was posted in CalculatedColumn, Sharepoint Online. Bookmark the permalink.