Counting weekdays between two dates

Hello,
Thanks to this video https://www.dundas.com/resources/off-the-charts-tips-from-an-expert/elevate-your-dashboards-reports/extend-your-data-with-dundasscript, I was able to count days between a start date and today’s date. Now I need to take out weekends and holidays…has anyone done anything like this before?

I did this in Tableua by doing some calculations on the number of weeks between the days, and then taking out 2 days per week. I had a separate calculation determining the number of holidays between start date and today, and subtracted that out as well. It wasn’t perfect, but got us “close enough”.

Once I have that, I’ll also need to do something similar and add a set number of work days to today to figure out when we’d expect to hit a target (in the future).

For more context, the use case is basically I have a target of $x, and I need to show how much of $x has happened to date, how much is left to go, and when we expect to hit $x, but using only workdays and not weekends and hoidays.

Thank you in advance!

1 Like

Hi Rachel,

I have had to do something similar, although not including holidays, and I think you may need to define what dates the holidays are, however to exclude weekends I created the following Calculated Element that may start you off:

if($completedate$==null) return;
else
{int StartD = $startdate$.DayOfWeek;
int completeD = $completedate$.DayOfWeek;
double calcBusinessDays =
1 + (($completedate$ - $startdate$).TotalDays * 5 -
(takenD - completeD) * 2) / 7;

if ($completedate$.DayOfWeek == DayOfWeek.Saturday) calcBusinessDays--;
if ($takendate$.DayOfWeek == DayOfWeek.Sunday) calcBusinessDays--;

return calcBusinessDays;

}

1 Like

Hi Rachel,

The script suggested by Chris should help you to eliminate the weekends. However, to determine the holidays, you’d have to do make an exception manually as we don’t have a specific method/function for it since every country or sometimes even provinces have different holidays.

1 Like

Thank you both for the responses!