Counting weekdays between two dates

#1

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
#2

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
#3

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
#4

Thank you both for the responses!