Working Days

[Ugo DI LUCA]

Hi,

After desperately trying to figure out and adapt Jason de Loose's calc posted on Experts some days ago, I went with my own brain with this calc.

The purpose is to easily adapt it to meet situations where working days could be other than typical week-ends dates, what surely is possible with Jason's...but he's playing with a bit of science I'm missing at the moment.

Case(not (IsEmpty(startDate) or IsEmpty(endDate));

Let([

extendedRange = (endDate+(7-DayOfWeek ( endDate )))-(startDate-(DayOfWeek ( startDate )-1));

tobeadded = (DayOfWeek ( startDate )-1)-(DayOfWeek ( startDate )>1)+(7-DayOfWeek ( endDate ))-(DayOfWeek ( endDate )<7)];

extendedRange+1-(((extendedRange+1)/7)*2)-tobeAdded))

which much more explained is

Let([

//checks that both dates aren't empty//

lock = not (IsEmpty(startDate) or IsEmpty(endDate));

//converts the range into a lager one beginning on [color:"red"]Sunday and ending the next [color:"green"]Saturday //

newgap = (endDate+([color:"green"]7 -DayOfWeek ( endDate )))-(startDate-(DayOfWeek ( startDate )-[color:"red"]1 ));

//adds one day so we now have full weeks from Sun to Sun, divide by 7 to get the total weeks, then muliply the weeks with the number of [color:"blue"] not working days//

we=((newgap+1)/7)* [color:"blue"] 2;

//evaluates number of days between the beginning [color:"red"]Sunday and the starting date, subtracting one through a boolean expression if the starting date is not a [color:"red"]Sunday //

from[color:"red"]Sun = (DayOfWeek ( startDate )- [color:"red"]1 )-(DayOfWeek ( startDate )>[color:"red"]1 );

//same process for the [color:"green"]Saturday side//

to[color:"green"]Sat = ([color:"green"]7 -DayOfWeek ( endDate ))-(DayOfWeek ( endDate )<[color:"green"]7 )];

//result is fulldays of that enlarged range less week-ends and the respective gaps from [color:"red"]Sunday and from [color:"green"]Saturday //

Case(lock;newgap+1-we-from[color:"red"]Sun -to[color:"green"]Sat ))

This calc works quite fine if I change the days (red and green) and if the not working days are following each other.

Anyone would like to try an adaptation to meet situations where days wouldn't follow (Thursday and Sunday for instance...) as I'm trying to have a generic calc to which I'd input parameters.