Count Saturdays an appointment ?

Hi

 

I have created this function which takes the start date, end date and weekday ( where 0 is Monday and 6 is Sunday)

 

function countDays(dateFrom : date,dateTo : date,dow : number) do
let countDays := days(dateFrom, dateTo) + 1;
let fullDays := floor(countDays / 7);
let partDays := countDays % 7;
let addDays := (7 - weekday(dateFrom) + dow) % 7;
let extraDay := number(addDays < partDays);
fullDays + extraDay
end

 

Regards John

Hi

 

Either Copy and paste your function into the Options / Global script definitions section and then in your formua put

countDays(DateFrom,DateTo,5)

5 tells it to count Saturdays.

Or just put this part of the code in your formula

let dow := 5;

let countDays := days(DateFrom, DateTo) + 1;
let fullDays := floor(countDays / 7);
let partDays := countDays % 7;
let addDays := (7 - weekday(dateFrom) + dow) % 7;
let extraDay := number(addDays < partDays);
fullDays + extraDay

 

Regards John

Hi

 

Let's take July 2021.

 

The number of days in the month, 31 is calculated by this line

let countDays := days(DateFrom, DateTo) + 1

There are at least 4 days for any day of the week, and this comes from dividing 31 by 7 and taking the whole number part

let fullDays := floor(countDays / 7)

There may be a 5th day. If we re-arrange the calendar we can see when that happens

 

We have 3 additional days, calculated by taking the modulo (the remainder) of 31 divided by 7 

let partDays := countDays % 7

How many days does it take us to get from the start date to our test date

let addDays := (7 - weekday(dateFrom) + dow) % 7

If it take more days than we have available the number of days will remain at 4, but if we can get there withing the number of days availabe there will be an extra 5th day

let extraDay := number(addDays < partDays)

Now add the two together to give us our answer

fullDays + extraDay

 

This is the tricky line

let addDays := (7 - weekday(dateFrom) + dow) % 7

You can see how it works here

 

This is saying

It take 5 days to get from Monday to Saturday, it take 1 day to get from Friday to Saturday, and it takes 6 days to get from Sunday to Saturday

 

Regards John