Share via

Calculating Working Hours between 2 dates in Sharepoint Online

  • Article

It is quite easy to do it if you are calculating standard hours. But if you mean working hours, without Saturday and Sunday, it isn't so easy to do it only with Calculating Column in Sharepoint

This scenario was tested on Sharepoint Online, but should work also in Sharepoint 2013 and 2010.

First let's assume some ideas:
Sunday is the first day in the wee, and Saturday the 7th
Working week starts Monday and stops Friday
Working day is from  8.30 am to 4.30 pm. No lunch break is included
The 2 columns needed are: StartDate and EndDate -  DateTime columns with Time

The scenario will calculate Days or working ours on 5 intervals:

Days - full working days

  1. Full working Days in first Week
  2. Full working Days in last Week
  3. Full working Days in full Weeks

Working Hours in:
4. First Day
5. Last Day

1. Full working Days in first Week:
You should create a CalculatedColumn - Numeric with 0 decimals
DAYS_FIRST_WEEK​ =IF(WEEKDAY(StartDate)=2;4;IF(WEEKDAY(StartDate)=3;3;IF(WEEKDAY(StartDate)=4;2;IF(WEEKDAY(StartDate)=5;1;0))))
**
2. Full working Days in last Week:
**You should create a CalculatedColumn - Numeric with 0 decimals
**DAYS_LAST_WEEK​=IF(WEEKDAY(EndDate)=3;1;IF(WEEKDAY(EndDate)=4;2;IF(WEEKDAY(EndDate)=5;3;IF(WEEKDAY(EndDate)=6;4;0))))

  1. Full working Days in full Weeks:
    **You will need to create 2 intermediate CalculatedColumns to get the first Sunday in the interval and also the last Saturday

For first Sunday, you will create a CalculatedColumn type DateTime

poz_start=IF(WEEKDAY(StartDate)=2;StartDate+6;IF(WEEKDAY(StartDate)=3;StartDate+5;IF(WEEKDAY(StartDate)=4;StartDate+4;IF(WEEKDAY(StartDate)=5;StartDate+3

For last Saturday, you will create a CalculatedColumn type DateTime
poz_end=IF(WEEKDAY(EndDate)=6;EndDate-6;IF(WEEKDAY(EndDate)=5;EndDate-5;IF(WEEKDAY(EndDate)=4;EndDate-4;IF(WEEKDAY(EndDate)=3;EndDate-3;IF(WEEKDAY(EndDate)=2;EndDate-2;IF(WEEKDAY(EndDate)=1;EndDate-1;EndDate)))))) ​
**
**Next, create other 2 CalculatedColumns - datetime **

**POZ_START_DATE **=DATE(YEAR(Poz_start);MONTH(Poz_start);DAY(Poz_start))

POZ_END_DATE*=DATE(YEAR(Poz_end);MONTH(Poz_end);DAY(Poz_end))*

The last column in this step is

​Days_in_full_Weeks=5*(IF((POZ_END_DATE-POZ_START_DATE)>=6;(POZ_END_DATE-POZ_START_DATE+1)/7;0))

**
**4. Working Hours in First Day:

****Create 2 intermediate CalculatedColumns - numeric ******

FirstWorkingHour_FirstDay​​=DATE(YEAR(StartDate);MONTH(StartDate);DAY(StartDate))+(60*8+30)/24/60

****LastWorkingHour_FirstDay=DATE(YEAR(StartDate);MONTH(StartDate);DAY(StartDate))+(60*16+30)/24/60
**

The number of working hours in first day it will be****
**
WorkingHours_FirstDay​ =(IF(OR(WEEKDAY(StartDate)=1;WEEKDAY(StartDate)=7);0;IF(1440*(LastWorkingHour_FirstDay-StartDate)>0;IF((FirstWorkingHour_FirstDay-StartDate)>0;540;1440*(LastWorkingHour_FirstDay-StartDate));0)))/60

****5. Working Hours in Last Day:

**********Create 2 intermediate CalculatedColumns - numeric **********

FirstWorkingHour_LastDay​​=DATE(YEAR(EndDate);MONTH(EndDate);DAY(EndDate))+(60*8+30)/24/60​​

**LastWorkingHour_La​stDay==DATE(YEAR(EndDate);MONTH(EndDate);DAY(EndDate))+(60*16+30)/24/60
**

The number of working hours in last day it will be**********
**
WorkingHours_​Last​Day​ =(IF(OR(WEEKDAY(EndDate)=1;WEEKDAY(EndDate)=7);0;IF(1440*(LastWorkingHour_LastDay-EndDate)>0;IF((FirstWorkingHour_LastDay-EndDate)>0;0;1440*(EndDate-FirstWorkingHour_LastDay));540)))/60

**After these steps, we have to add all these 5 values, and the result will be
**TOTAL=​8*(DAYS_FIRST_WEEK+Days_in_Full_Weeks+DAYS_LAST_WEEK)+WorkingHours_FirstDay+WorkingHours_LastDay

For more details about the process, check this link************