Date difference excluding weekends and bankholidays

Ask for Help
1

Hello
We are trying to set uo a leave request app.
As it goes, we are unable to exclude week-ends and bank holidays. (date2 - date1)
We have tried through an Excel formula that does this, but it does not work in the glide table, so we tried Javascript but here again the code does not run…
Is there another option we didn’t think about ?

Thank you

2

Is the goal to calculate the number of days leave between a given start and end date, excluding weekends and public holidays?

You can use a couple of helper tables with a bit of Glide Date math to do that. If you confirm that is the goal, I can show you how to do it.

3

@Lise_Delarue - here…

5 Likes
4

It’s the goal, indeed.

5

Hola,

Glide’s Excel plugin has a function that can help you:
NETWORKDAYS(Date1, Date2[, Holidays])

Saludos!

1 Like
6

Thank You Darren.
It worked well.
Learned a lot.

7

I will give it a try. Thank you.

8

Beautiful.

9

Hi Darren,

We finally used javascript to sort it out.

let startDate = new Date(p1);
let endDate = new Date(p2);
endDate = new Date(endDate.getTime()+360010002);
let currentDate = new Date(startDate.getTime()+360010002);
let dayCount=0;

let an = currentDate.getFullYear();

let JourAn = new Date(an, “00”, “01”);
JourAn = new Date(JourAn.getTime()+360021000);
let FeteTravail = new Date(an, “04”, “01”);
FeteTravail = new Date(FeteTravail.getTime() + 360021000);
let Victoire1945 = new Date(an, “04”, “08”);
Victoire1945 = new Date(Victoire1945.getTime() + 360021000);
let FeteNationale = new Date(an,“06”, “14”);
FeteNationale = new Date(FeteNationale.getTime() + 360021000);
let Assomption = new Date(an, “07”, “15”);
Assomption = new Date(Assomption.getTime() + 360021000);
let Toussaint = new Date(an, “10”, “01”);
Toussaint = new Date(Toussaint.getTime() + 360021000);
let Armistice = new Date(an, “10”, “11”);
Armistice = new Date(Armistice.getTime() + 360021000);
let Noel = new Date(an, “11”, “25”);
Noel = new Date(Noel.getTime() + 360021000);

let G = an%19;
let C = Math.floor(an/100);
let H = (C - Math.floor(C/4) - Math.floor((8*C+13)/25) + 19*G + 15)%30;
let I = H - Math.floor(H/28)*(1 - Math.floor(H/28)*Math.floor(29/(H + 1))*Math.floor((21 - G)/11));
let J = (an*1 + Math.floor(an/4) + I + 2 - C + Math.floor(C/4))%7
let L = I - J;
let MoisPaques = 3 + Math.floor((L + 40)/44);
let JourPaques = L + 28 - 31*Math.floor(MoisPaques/4);
let Paques = new Date(an, MoisPaques-1, JourPaques);
Paques = new Date(Paques.getTime() + 3600*2*1000);
let LundiPaques = new Date(an, MoisPaques-1, JourPaques+1);
LundiPaques = new Date(LundiPaques.getTime() + 3600*2*1000);
let Ascension = new Date(an, MoisPaques-1, JourPaques+39);
Ascension = new Date(Ascension.getTime() + 3600*2*1000);
let Pentecote = new Date(an, MoisPaques-1, JourPaques+49);
Pentecote = new Date(Pentecote.getTime() + 3600*2*1000);
let LundiPentecote = new Date(an, MoisPaques-1, JourPaques+50);
LundiPentecote = new Date(LundiPentecote.getTime() + 3600*2*1000);

let joursFeries =  new Array(JourAn.getDate()+"-"+JourAn.getMonth(), Paques.getDate()+"-"+Paques.getMonth(), LundiPaques.getDate()+"-"+LundiPaques.getMonth(),

FeteTravail.getDate()+“-”+FeteTravail.getMonth(),
Victoire1945.getDate()+“-”+Victoire1945.getMonth(),
Ascension.getDate()+“-”+Ascension.getMonth(),
Pentecote.getDate()+“-”+Pentecote.getMonth(),
LundiPentecote.getDate()+“-”+LundiPentecote.getMonth(),
FeteNationale.getDate()+“-”+FeteNationale.getMonth(),
Assomption.getDate()+“-”+Assomption.getMonth(),
Toussaint.getDate()+“-”+Toussaint.getMonth(),
Armistice.getDate()+“-”+Armistice.getMonth(),
Noel.getDate()+“-”+Noel.getMonth()
);

if(currentDate>endDate){
return “erreur de date”}

if(startDate.getFullYear()!=endDate.getFullYear()){
return"année de début et de fin différente"
}

while(currentDate <= endDate) {

if(currentDate.getDay()!=0 && currentDate.getDay()!=6 && !joursFeries.includes(currentDate.getDate()+“-”+currentDate.getMonth())){

dayCount++;
}
currentDate = new
Date(currentDate.getTime()+246060*1000);

}

return dayCount;

I was helped by a friend for that one.

Got another problem to solve : The number of days left (total number of leaves gained minus a roll up on those that were accepted ) when the demands are accepted only show when the user id is the current user id. Which means that admins can see their records but not the records of the persons that asked for the leave. I opened the app to copy so that you can have a look.
Don’t know how to do that.
Lise

10

oh, my… did you really use all that JavaScript in preference to the solution that I gave you?

Totally your choice of course. But gee whiz, just looking at that makes my head spin. Good luck maintaining that, and debugging it when it breaks :wink:

Anyway, I’ll see if I can take a look at your app later today.

3 Likes
11

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.