Forum Discussion

Milozebre's avatar
Milozebre
Icon for Helper V rankHelper V
8 years ago
Solved

Working days and holidays

Hello Community, 

 

I have a question about wokring days. 

Before you send me links here, i read it and thats why i post this question. 

I read the page of Alberto Ferrari : http://sqlblog.com/blogs/alberto_ferrari/archive/2011/01/19/working-days-computation-in-powerpivot.aspx

 

After that, I create a calendar and an another table with all holidays here  :

Jour Férié (France)Annee Jour semaineJourDateComplèteMoisDate
Jour de l'an20180Lundilundi 1 janvier 2018Janvier1 janvier 2018
Pâques20186Dimanchedimanche 12 avril 2020Avril12 avril 2020
Lundi de Pâques20180Lundilundi 13 avril 2020Avril13 avril 2020
Fête du Travail20181Vendredivendredi 1 mai 2020Mai1 mai 2020
Armistice 194520181Vendredivendredi 8 mai 2020Mai8 mai 2020

 

I make realtion between my calendar and my holidays table, and its working. 

I begin with the Excel Working Days : and it doesnt works .

IF (OR ([@WeekDay] = 6, [@WeekDay] = 7), 0, IF (ISNA (VLOOKUP ([@Date], HolidaysTable[#Data], 2, FALSE)), 1, 0))

The first part is working : IF (OR ([@WeekDay] = 6, [@WeekDay] = 7), 0, 1) I have the good result. But how can i check in my jours fériés table if the date are present and if its the case then it's not a working days. 

 

Thank you in advance