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:
JobStart
JobPause
JobResume
JobStop

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
OR(YEAR(JobPause)=1899;YEAR(JobResume)=1899)

If the condition is TRUE, the formula is quite simple to show hours and minutes
TEXT(JobStop-JobSTart;”h:mm”)

If it isn’t we need to calculate minutes betweeb JobStop and JobStart and then substract the lunch break JobResumeJobPause
INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440)

Starting from this we need to calculate complete hours as integer from minutes divided by 60
INT((INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440))/60)
and concatenate with “;” and remaining minutes in non-complete hours
INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440)-60*INT((INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440))/60)

Because the result is a text, wee need also to add a zero digit if minutes are less then 10
IF(INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440)-60*INT((INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440))/60)>9…..

So, the complete formula is this one:
=IF(OR(YEAR(JobPause)=1899;YEAR(JobResume)=1899);TEXT(JobStop-JobSTart;”h:mm”);CONCATENATE(INT((INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440))/60);”:”;IF(INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440)-60*INT((INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440))/60)>9;INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440)-60*INT((INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440))/60);CONCATENATE(“0”;INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440)-60*INT((INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440))/60)))))

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