4 Replies Latest reply on Mar 22, 2011 11:05 AM by guest contributor

# calculate total sales base on work days

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

• ###### 1. Re: calculate total sales base on work days

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?

• ###### 2. Re: calculate total sales base on work days

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

• ###### 3. Re: calculate total sales base on work days

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

• ###### 4. Re: calculate total sales base on work days

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)

2 of 2 people found this helpful