# Date difference excluding weekends and bankholidays
**URL:** https://community.glideapps.com/t/date-difference-excluding-weekends-and-bankholidays/55385
**Category:** Ask for Help
**Created:** [December 4, 2022, 7:50am UTC](https://community.glideapps.com/t/date-difference-excluding-weekends-and-bankholidays/55385 "2022-12-04T07:50:48Z")
**Posts on this page:** 11
**Page:** 1
### Author:  [@Lise\_Delarue](https://community.glideapps.com/u/Lise_Delarue)
#### Post date: [December 4, 2022, 7:50am UTC](https://community.glideapps.com/t/date-difference-excluding-weekends-and-bankholidays/55385/1 "2022-12-04T07:50:48Z")
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
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [December 4, 2022, 8:00am UTC](https://community.glideapps.com/t/date-difference-excluding-weekends-and-bankholidays/55385/2 "2022-12-04T08:00:13Z")
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.
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [December 4, 2022, 9:37am UTC](https://community.glideapps.com/t/date-difference-excluding-weekends-and-bankholidays/55385/3 "2022-12-04T09:37:42Z")
@Lise_Delarue - here…
https://www.loom.com/embed/925842b9377941a6a1debbd914fb7409?sid=572d5cb5-4559-44b8-b0ff-d7eb06251c72
---
### Author:  [@Lise\_Delarue](https://community.glideapps.com/u/Lise_Delarue)
#### Post date: [December 4, 2022, 4:35pm UTC](https://community.glideapps.com/t/date-difference-excluding-weekends-and-bankholidays/55385/4 "2022-12-04T16:35:42Z")
It’s the goal, indeed.
---
### Author:  [@gvalero](https://community.glideapps.com/u/gvalero)
#### Post date: [December 4, 2022, 6:34pm UTC](https://community.glideapps.com/t/date-difference-excluding-weekends-and-bankholidays/55385/5 "2022-12-04T18:34:29Z")
Hola,
Glide’s Excel plugin has a function that can help you:
NETWORKDAYS(Date1, Date2[, Holidays])
Saludos!
---
### Author:  [@Lise\_Delarue](https://community.glideapps.com/u/Lise_Delarue)
#### Post date: [December 4, 2022, 6:55pm UTC](https://community.glideapps.com/t/date-difference-excluding-weekends-and-bankholidays/55385/6 "2022-12-04T18:55:17Z")
Thank You Darren.
It worked well.
Learned a lot.
---
### Author:  [@Lise\_Delarue](https://community.glideapps.com/u/Lise_Delarue)
#### Post date: [December 4, 2022, 6:58pm UTC](https://community.glideapps.com/t/date-difference-excluding-weekends-and-bankholidays/55385/7 "2022-12-04T18:58:01Z")
> [@gvalero](#):
>
> Glide’s Excel plugin has a function that can help you:
> NETWORKDAYS(Date1, Date2[, Holidays])
I will give it a try. Thank you.
---
### Author:  [@nathanaelb](https://community.glideapps.com/u/nathanaelb)
#### Post date: [December 4, 2022, 7:22pm UTC](https://community.glideapps.com/t/date-difference-excluding-weekends-and-bankholidays/55385/8 "2022-12-04T19:22:21Z")
Beautiful.
---
### Author:  [@Lise\_Delarue](https://community.glideapps.com/u/Lise_Delarue)
#### Post date: [December 27, 2022, 5:47pm UTC](https://community.glideapps.com/t/date-difference-excluding-weekends-and-bankholidays/55385/9 "2022-12-27T17:47:46Z")
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()+3600_1000_2);
let currentDate = new Date(startDate.getTime()+3600_1000_2);
let dayCount=0;
let an = currentDate.getFullYear();
let JourAn = new Date(an, “00”, “01”);
JourAn = new Date(JourAn.getTime()+3600_2_1000);
let FeteTravail = new Date(an, “04”, “01”);
FeteTravail = new Date(FeteTravail.getTime() + 3600_2_1000);
let Victoire1945 = new Date(an, “04”, “08”);
Victoire1945 = new Date(Victoire1945.getTime() + 3600_2_1000);
let FeteNationale = new Date(an,“06”, “14”);
FeteNationale = new Date(FeteNationale.getTime() + 3600_2_1000);
let Assomption = new Date(an, “07”, “15”);
Assomption = new Date(Assomption.getTime() + 3600_2_1000);
let Toussaint = new Date(an, “10”, “01”);
Toussaint = new Date(Toussaint.getTime() + 3600_2_1000);
let Armistice = new Date(an, “10”, “11”);
Armistice = new Date(Armistice.getTime() + 3600_2_1000);
let Noel = new Date(an, “11”, “25”);
Noel = new Date(Noel.getTime() + 3600_2_1000);
```
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()+24_60_60\*1000);
}
return dayCount;
I was helped by a friend for that one.
> **[Appli Congés 2023](https://conges-2023-e2c.glideapp.io)**
>
> Poser et suivre les demandes de congés
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
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [December 28, 2022, 2:52am UTC](https://community.glideapps.com/t/date-difference-excluding-weekends-and-bankholidays/55385/10 "2022-12-28T02:52:11Z")
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 😉
Anyway, I’ll see if I can take a look at your app later today.
---
### Author:  [@system](https://community.glideapps.com/u/system)
#### Post date: [January 20, 2023, 8:13am UTC](https://community.glideapps.com/t/date-difference-excluding-weekends-and-bankholidays/55385/11 "2023-01-20T08:13:31Z")
This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.