Calculating Working Hours – days in first week

 

…this post was published on 2016 March 31.

Let’s start with the beginning. And because an image it’s like 1000 words:

The user filled the start day for Wednesday let’s suppose. For this first Week, the image looks like:
Because we need to calculate working hours, for the moment we will forget this first not full day. The analyze and the calculation for this first day will be done in last steps.
So, what we need to calculate, is the number of full working days from StartDate ( in this case W ) to Saturday.
I ‘ve created a new column, a calculated column as a number, no decimals –  with the name DAYS_FIRST_WEEK​ ( this mean: full working days in first week ).
The calculation formula will analyse which day in the week is the StartDate, and based on that will set this column value. In our image, you can see , that if SartDate ( first day ) it’s We​dnesday​, the remaining full working days are 2 ( Thursday and Friday )
this mean:

StartDate DAYS_FIRST_WEEK​
Monday 4​
Tuesday ​ 3​
Wednesday 2​
Thursday​ 1​
Friday 0​
Saturday​ 0​
Sunday 0​
​​

A formula to get these values will looks like:

=IF(WEEKDAY(StartDate)=2;4;IF(WEEKDAY(StartDate)=3;3;IF(WEEKDAY(StartDate)=4;2;IF(WEEKDAY(StartDate)=5;1;0))))
…and the values are:
Main topic Next Step
This entry was posted in CalculatedColumn, Sharepoint Online. Bookmark the permalink.