Calculating Working Hours

  …this post was published on 2016 March 31.

between 2 dates in Sharepoint using only Calculated Columns.

Well this is a challenge. Not a really new one ( because it was another topic but not for hours, only for working days )

Before staring let me put some considerations

1​. Working week – between Monday to Friday

2. The weekdays are:
Monday = 2
​Tuesday = 3
Wednesday=4
​Thursday=5
Friday=6
​Saturday=7
Sunday=1

3.The working day starts at 8.30 in the morning. This value will be used directly in calculation formula – presented in red color.

4. The working day stops at 4.30 pm. This value will be used directly in calculation formula – presented in red color.

5. No lunch break is calculated.

6. The main columns , filled by the user are
StartDate –  DateTime type column
     EndDate – DateTime type column

7.  All other columns will be calculated, and could be hidden in views

8. No extra working time will be calculated – To avoid extra-time, you can use validation formula in StartDate and/or EndDate columns.

9. Week image –  I will use this image in next steps, to be more intuitive.


To start, I will split the explanations in multiple posts, to be clear and to avoid some mistakes.

1. Calculating Working Hours – days in first week​
2. Calculating Working Hours – days in last week
3. Calculating Working Hours – days in full weeks
4. Calculating Working Hours – hours in first day​
​5. Calculating Working Hours – hours in last day
6. Calculating Working Hours – total working hours

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