Excluding weekends from the difference of dates.

Question

Hi there,&nbsp;I have a date table, one of the columns contains the name of week name. I want to calculate the number of days between the start date and end date and exclude the weekend. Below is the formula:Difference in days =&nbsp;CALCULATE( COUNTROWS(Dates),&nbsp;FILTER(ALL(Dates), Dates[Date] &gt;= start_date &amp;&amp;Dates[Date] &lt;= end_date &amp;&amp;&nbsp; Dates[IsHoliday] = FALSE() &amp;&amp;&nbsp; (Dates[Day Of Week Name] &lt;&gt; "Saturday" &amp;&amp;Dates[Day Of Week Name] &lt;&gt; "Sunday")&nbsp; )&nbsp; )&nbsp;However, it did not return the right number of days. Could anyone guide me where am I made the mistake?&nbsp;&nbsp;&nbsp;

anonymous · Answer

HI&nbsp;Dunner2020&nbsp;My bad, I didn't scroll to the right.I got 22 as a result. My [Is Holiday] column is Text and used the below formula. You can change the [start date], [end date] and make sure they are in the correct format and compared with Dates[Date] column.&nbsp;Difference in days = CALCULATE( COUNTROWS(Dates), FILTER(ALL(Dates)
                            , Dates[Date] &gt;= DATE(2019,01,01) &amp;&amp;
                              Dates[Date] &lt;= date(2019,01,31) &amp;&amp;  Dates[IsHoliday] = "FALSE" 
                              &amp;&amp;  (Dates[Day Of Week Name] &lt;&gt; "Saturday" &amp;&amp;
Dates[Day Of Week Name] &lt;&gt; "Sunday")  )  )&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;Dunner2020&nbsp;Can you please post some sample data for atleast 10 days?

dunner2020 · Answer

Hi&nbsp;Anonymous&nbsp;,&nbsp;Here is the sample data:&nbsp;DateYearQuarter Of YearMonth Of YearDay Of MonthDateIntMonth NameMonth In CalendarQuarter In CalendarDay In WeekDay Of Week NameWeek EndingWeek NumberMonth In YearQuarternYearShort YearRYIsHoliday1/01/2019201911120190101JanuaryJan 2019Q1 20191Tuesday########1201901002019010019RY19False2/01/2019201911220190102JanuaryJan 2019Q1 20192Wednesday########1201901002019010019RY19False3/01/2019201911320190103JanuaryJan 2019Q1 20193Thursday########1201901002019010019RY19False4/01/2019201911420190104JanuaryJan 2019Q1 20194Friday########1201901002019010019RY19False5/01/2019201911520190105JanuaryJan 2019Q1 20195Saturday########1201901002019010019RY19False6/01/2019201911620190106JanuaryJan 2019Q1 20196Sunday########1201901002019010019RY19False7/01/2019201911720190107JanuaryJan 2019Q1 20190Monday########2201901002019010019RY19False8/01/2019201911820190108JanuaryJan 2019Q1 20191Tuesday########2201901002019010019RY19False9/01/2019201911920190109JanuaryJan 2019Q1 20192Wednesday########2201901002019010019RY19False10/01/20192019111020190110JanuaryJan 2019Q1 20193Thursday########2201901002019010019RY19False11/01/20192019111120190111JanuaryJan 2019Q1 20194Friday########2201901002019010019RY19False12/01/20192019111220190112JanuaryJan 2019Q1 20195Saturday########2201901002019010019RY19False13/01/20192019111320190113JanuaryJan 2019Q1 20196Sunday########2201901002019010019RY19False14/01/20192019111420190114JanuaryJan 2019Q1 20190Monday########3201901002019010019RY19False15/01/20192019111520190115JanuaryJan 2019Q1 20191Tuesday########3201901002019010019RY19True16/01/20192019111620190116JanuaryJan 2019Q1 20192Wednesday########3201901002019010019RY19False17/01/20192019111720190117JanuaryJan 2019Q1 20193Thursday########3201901002019010019RY19False18/01/20192019111820190118JanuaryJan 2019Q1 20194Friday########3201901002019010019RY19False19/01/20192019111920190119JanuaryJan 2019Q1 20195Saturday########3201901002019010019RY19False20/01/20192019112020190120JanuaryJan 2019Q1 20196Sunday########3201901002019010019RY19False21/01/20192019112120190121JanuaryJan 2019Q1 20190Monday########4201901002019010019RY19False22/01/20192019112220190122JanuaryJan 2019Q1 20191Tuesday########4201901002019010019RY19False23/01/20192019112320190123JanuaryJan 2019Q1 20192Wednesday########4201901002019010019RY19False24/01/20192019112420190124JanuaryJan 2019Q1 20193Thursday########4201901002019010019RY19False25/01/20192019112520190125JanuaryJan 2019Q1 20194Friday########4201901002019010019RY19False26/01/20192019112620190126JanuaryJan 2019Q1 20195Saturday########4201901002019010019RY19False27/01/20192019112720190127JanuaryJan 2019Q1 20196Sunday########4201901002019010019RY19False28/01/20192019112820190128JanuaryJan 2019Q1 20190Monday########5201901002019010019RY19False29/01/20192019112920190129JanuaryJan 2019Q1 20191Tuesday########5201901002019010019RY19False30/01/20192019113020190130JanuaryJan 2019Q1 20192Wednesday########5201901002019010019RY19False31/01/20192019113120190131JanuaryJan 2019Q1 20193Thursday########5201901002019010019RY19False

anonymous · Answer

HI&nbsp;Dunner2020&nbsp;It is missing&nbsp;Dates[IsHoliday] column.

dunner2020 · Answer

Anonymous&nbsp;,&nbsp;That's strange. Because I can see IsHoliday column. IsHoliday contains either False or True Values.In the sample, all values of IsHoliday is false except one date i.e. 15/1/2019

Forum Discussion

Excluding weekends from the difference of dates.

8 Replies

Related content

DATEDIFF excluding weekends

Previous Day Balance - Excluding Weekends

Date / Time difference excluding weekends and factoring working hours

Exclude weekends from Date Measure

calculations excluding weekends

Date	Year	Quarter Of Year	Month Of Year	Day Of Month	DateInt	Month Name	Month In Calendar	Quarter In Calendar	Day In Week	Day Of Week Name	Week Ending	Week Number	Month In Year	QuarternYear	Short Year	RY	IsHoliday
1/01/2019	2019	1	1	1	20190101	January	Jan 2019	Q1 2019	1	Tuesday	########	1	20190100	20190100	19	RY19	False
2/01/2019	2019	1	1	2	20190102	January	Jan 2019	Q1 2019	2	Wednesday	########	1	20190100	20190100	19	RY19	False
3/01/2019	2019	1	1	3	20190103	January	Jan 2019	Q1 2019	3	Thursday	########	1	20190100	20190100	19	RY19	False
4/01/2019	2019	1	1	4	20190104	January	Jan 2019	Q1 2019	4	Friday	########	1	20190100	20190100	19	RY19	False
5/01/2019	2019	1	1	5	20190105	January	Jan 2019	Q1 2019	5	Saturday	########	1	20190100	20190100	19	RY19	False
6/01/2019	2019	1	1	6	20190106	January	Jan 2019	Q1 2019	6	Sunday	########	1	20190100	20190100	19	RY19	False
7/01/2019	2019	1	1	7	20190107	January	Jan 2019	Q1 2019	0	Monday	########	2	20190100	20190100	19	RY19	False
8/01/2019	2019	1	1	8	20190108	January	Jan 2019	Q1 2019	1	Tuesday	########	2	20190100	20190100	19	RY19	False
9/01/2019	2019	1	1	9	20190109	January	Jan 2019	Q1 2019	2	Wednesday	########	2	20190100	20190100	19	RY19	False
10/01/2019	2019	1	1	10	20190110	January	Jan 2019	Q1 2019	3	Thursday	########	2	20190100	20190100	19	RY19	False
11/01/2019	2019	1	1	11	20190111	January	Jan 2019	Q1 2019	4	Friday	########	2	20190100	20190100	19	RY19	False
12/01/2019	2019	1	1	12	20190112	January	Jan 2019	Q1 2019	5	Saturday	########	2	20190100	20190100	19	RY19	False
13/01/2019	2019	1	1	13	20190113	January	Jan 2019	Q1 2019	6	Sunday	########	2	20190100	20190100	19	RY19	False
14/01/2019	2019	1	1	14	20190114	January	Jan 2019	Q1 2019	0	Monday	########	3	20190100	20190100	19	RY19	False
15/01/2019	2019	1	1	15	20190115	January	Jan 2019	Q1 2019	1	Tuesday	########	3	20190100	20190100	19	RY19	True
16/01/2019	2019	1	1	16	20190116	January	Jan 2019	Q1 2019	2	Wednesday	########	3	20190100	20190100	19	RY19	False
17/01/2019	2019	1	1	17	20190117	January	Jan 2019	Q1 2019	3	Thursday	########	3	20190100	20190100	19	RY19	False
18/01/2019	2019	1	1	18	20190118	January	Jan 2019	Q1 2019	4	Friday	########	3	20190100	20190100	19	RY19	False
19/01/2019	2019	1	1	19	20190119	January	Jan 2019	Q1 2019	5	Saturday	########	3	20190100	20190100	19	RY19	False
20/01/2019	2019	1	1	20	20190120	January	Jan 2019	Q1 2019	6	Sunday	########	3	20190100	20190100	19	RY19	False
21/01/2019	2019	1	1	21	20190121	January	Jan 2019	Q1 2019	0	Monday	########	4	20190100	20190100	19	RY19	False
22/01/2019	2019	1	1	22	20190122	January	Jan 2019	Q1 2019	1	Tuesday	########	4	20190100	20190100	19	RY19	False
23/01/2019	2019	1	1	23	20190123	January	Jan 2019	Q1 2019	2	Wednesday	########	4	20190100	20190100	19	RY19	False
24/01/2019	2019	1	1	24	20190124	January	Jan 2019	Q1 2019	3	Thursday	########	4	20190100	20190100	19	RY19	False
25/01/2019	2019	1	1	25	20190125	January	Jan 2019	Q1 2019	4	Friday	########	4	20190100	20190100	19	RY19	False
26/01/2019	2019	1	1	26	20190126	January	Jan 2019	Q1 2019	5	Saturday	########	4	20190100	20190100	19	RY19	False
27/01/2019	2019	1	1	27	20190127	January	Jan 2019	Q1 2019	6	Sunday	########	4	20190100	20190100	19	RY19	False
28/01/2019	2019	1	1	28	20190128	January	Jan 2019	Q1 2019	0	Monday	########	5	20190100	20190100	19	RY19	False
29/01/2019	2019	1	1	29	20190129	January	Jan 2019	Q1 2019	1	Tuesday	########	5	20190100	20190100	19	RY19	False
30/01/2019	2019	1	1	30	20190130	January	Jan 2019	Q1 2019	2	Wednesday	########	5	20190100	20190100	19	RY19	False
31/01/2019	2019	1	1	31	20190131	January	Jan 2019	Q1 2019	3	Thursday	########	5	20190100	20190100	19	RY19	False