Calculating Working Hours – days in last week

…this post was published on 2016 March 31.

Let’s assume that EndDate is Thursday as bellow

To calculate full working days in last week, I’ve created a calculated column , numeric type with 0 decimals. I named it: ​DAYS_LAST_WEEK ( Full working days in last week ) It is similar to DAYS_FIRST_WEEK in previous post​​​.

In this case we need to calculate the number of full working days starting from Monday to EndDate, in this image Thursday.- in our case is 3.

The values, deppending on EndDate filled by user, will be:

EndDate DAYS_LAST_WEEK ​
​Monday​ 0​
Tuesday​ 1​
Wednesday​ 2​
Thursday​ 3​
Friday​ 4​
Saturday​ 0​
Sunday​ 0​
The calculation formula in this case is:
=IF(WEEKDAY(EndDate)=3;1;IF(WEEKDAY(EndDate)=4;2;IF(WEEKDAY(EndDate)=5;3;IF(WEEKDAY(EndDate)=6;4;0))))

The question is, why if the last day is Sunday or Saturday, the DAYS_LAST_WEEK ​​=0?

Because in this case, this last week will be a full week and will be calculated in the next post: Calculating Working Hours – days is full weeks​

And results are bellow:
Previous Step Main topic Next Step
This entry was posted in Uncategorized. Bookmark the permalink.