hello all,
i need to figure out how to calculate the number of work days that pass from the beginning of the month relative to the current day base on excel file. for exmple, if today is 16/02/2011 so how much work days pass until 16.(ignore Friday and Saturday and holidays)
thank
gavriel
In Excel you could just use networkday(today, date truncated to start of month). In Tableau there is no date function that is calendar aware. There will be a calculation that could be created, but do you just have the ability to count the dates that have a sale associated with them and avoid this?
How technically can we use the Excel as an additional data(Workdays for each month)
To calculate relatively the working days for each invoice that are in another source
Thks
You could create an excel sheet with every date going forwards from now (10 years?), and use networkday to provide this information, then tie the two together - kind of clunky though
I cannot do anything with the holidays, but the formula would be this for just finding work days. (Taken from the following post
http://superuser.com/questions/35716/equivalent-of-excels-networkdays-function-with-jet-ado
and fitted it to use with Tableau.)
([Last Date]-([FirstDate] +
(DATEPART('weekday',[Last Date])-DATEPART('weekday',[First Date]))))/7*5 +
(DATEPART('weekday',[Last Date])-DATEPART('weekday',[First Date])) + 1 +
IIf(DATEPART('weekday',[Last Date])=7,-1,0)+IIf(DATEPART('weekday',[First Date])=1,-1,0)