Calculating Working Hours – days in full weeks

…this post was published on 2016 March 31.

Well, this is the hardest step in this scenario because we need to analyze the interval between StartDate and EndDate, to get the number of full weeks.

The structure could looks like below:

To get full weeks number, we need first to calculate the first Sunday in the interval, this mean the first Sunday after StartDate

I created a new column, a calculated column as a DateTime column ​​​ with the name​ poz_start ( Start position for full weeks  )


In our case, if StartDate is Wednesday, we need to add 4 more days to get the first Sunday. The table with needed values:

StartDate Days to add
Monday​ 6​
Tuesday​ 5​
Wednesday​ 4​
Thursday​ 3​
Friday​ 2
Saturday​ 1
Sunday​
0

The formula for poz_start is:
=IF(WEEKDAY(StartDate)=2;StartDate+6;IF(WEEKDAY(StartDate)=3;StartDate+5;IF(WEEKDAY(StartDate)=4;StartDate+4;IF(WEEKDAY(StartDate)=5;StartDate+3

The same idea for last week: we need to come back to last Saturday before EndDate

To store that Last Saturday, I’ve created a calculated column , as DateTime – the name for it is: ​poz_end ( End position for full weeks )

In our example, if EndDate is Thursday, we need to substract 5 days to get first Saturday before EndDate

 

EndDate Days to substract
Monday​ ​2
​Tuesday 3​
Wednesday​ 4​
Thursday​ ​5
​Friday ​6
​Saturday 1
​Sunday 0​

The needed formula to calculate poz_end column is:

=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))))))

At this time, our picture looks like:

 We need right now to calculate the number of full weeks: N , between poz_start and poz_end.
BUT because StartDate and EndDate contain different times, poz_start and poz_end also, any result using them could be wrong. To eliminate this issue, wee need to calculate 2 new columns  with names POZ_START_DATE and POZ_END_DATE, starting from parent columns but setting time value equal to 0:00.

These 2 columns are:

The formula for each column is:

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))

Now we can calculate the N number of full weeks between poz_start and poz_end using POZ_START_DATE and POZ_END_DATE

The formula is:
=5*(IF((POZ_END_DATEPOZ_START_DATE)>=6;(POZ_END_DATEPOZ_START_DATE+1)/7;0))

and it will be stored in a calculated column ​Days_in_full_Weeks, with value as numbers with 0 decimals.
The explanation for this formula is:
If beetween StartDate end EndDate is zero full weeks- in this case poz_end will be less than poz_start ( -1 ) If between these days are at least one full week, the difference will be at least 6
The values in the list are:

Previous Step Main topic Next Step
This entry was posted in CalculatedColumn, Sharepoint Online, Uncategorized. Bookmark the permalink.