Forum Discussion
Count days between two dates excluding weekends and holidays
Hi JagThripp,
In my opinion, to count working days between two dates excluding weekends and holidays. We need to create calendar table to mark the holidays. So that we can refer to this calendar table and exclude the weekends and holidays in your Fact table. Something like:
With current detail Holidays table, it is hard to create relationship between it with your Fact table since the holidays in each year are separated in multiple columns.
So in your scenario, I think you can go to Edit Queries. Duplicate your Holidays table to multiple ones and for each one keep one year column and remove others. Then you can Append these new queries to make all the dates in one same column. By doing this, you can create a new Holidays table with all the holidays in one same column.
Thanks,
Xi Jin.
Solution SageHi JagThripp,
In my opinion, to count working days between two dates excluding weekends and holidays. We need to create calendar table to mark the holidays. So that we can refer to this calendar table and exclude the weekends and holidays in your Fact table. Something like:
With current detail Holidays table, it is hard to create relationship between it with your Fact table since the holidays in each year are separated in multiple columns.
So in your scenario, I think you can go to Edit Queries. Duplicate your Holidays table to multiple ones and for each one keep one year column and remove others. Then you can Append these new queries to make all the dates in one same column. By doing this, you can create a new Holidays table with all the holidays in one same column.
Thanks,
Xi Jin.
Hi Xin,
I have modified the holidays table to be one column of dates formatted as dd/mm/yy.
What I seem to be missing is how this helps to calculate the number of "valid working" days between the created date and "NOW()".
I have looked at the examples you posted, but with my data I fail to see how to apply this methodology.
Jag
Jag,
I am in the same boat, did you ever have any success with this?
Thanks!
