5 Replies Latest reply on Sep 26, 2013 12:40 AM by Prashant Sharma

# DATEADD only business days (i.e. Monday - Friday)

Hello All,

Issue: Want to report the expected date when a customer will finish the number of daily payment installments.

Ex: Product funds on Sept 29, and there are 30 total daily installments. Since customers can only make payments on BUSINESS days, I the expected payment would be finished 30 business days from September 29th. (Monday - Friday) So the expected date would NOT be October 29th (which is what Tableau would give me), but rather November 14, or 15. (Something like that)

What I need:

A DATEADD formula that will add BUSINESS DAYS to a given date for a more accurate expected date of finished payments.

If someone could help me out with this, this may save me.

Thanks,

Kenny

• ###### 1. Re: DATEADD only business days (i.e. Monday - Friday)

You will find some useful info here: http://community.tableau.com/thread/118000?start=0&tstart=0

If you can post a sample packaged workbook demonstrating what you currently have (incorrect results), someone on the forum can help you arrive at the accurate results.

• ###### 2. Re: DATEADD only business days (i.e. Monday - Friday)

Hi Kenny,

I am able to do a workaround for you. It is a bit complex and funny

I have created 4 simple calculations rather than creating a complex single calculation.

1. Start Weekday : Calculating based on the '01-01-1900' , (datediff('day',#01/01/1900#,[StartDate])+2)%7) Adding +2 because '01-01-1900'  actual week day is 2.

2. Current Week Working Days : Calculating the No of working days on the start date week

3. Total No of days: Total no of days from start date to End Date.

4. End Date = Start Date+ Total No of Week days

I think this solution will solve your working days issue but you have another challenge how do you remove National holidays from it?

• ###### 3. Re: DATEADD only business days (i.e. Monday - Friday)

Indumon,

Thanks for a prompt response. Maybe this screenshot down here will help you better. What I need is a way to add the fourth column (number of daily payments) to the second column (Today()) which is my startdate). Since a customer can only make 1 payment a day, but NOT on the weekends, is there a formula for DateADD that will get me the actual expected date? This will be a relative formula, so TODAY()) will always be my starttdate. The expected date I have in here does not account for excluding weekends, which is why it is so much earlier than the actual expected date should be.

1 of 1 people found this helpful
• ###### 4. Re: DATEADD only business days (i.e. Monday - Friday)

Kenny, We can apply the same logic in your workbook too. Is it possible to upload a packaged workbook of this screen shot?

• ###### 5. Re: DATEADD only business days (i.e. Monday - Friday)

Hi Kenny,

With the following calculation you can find only weekdays.

if [Order Date]>[order start] and [Order Date]<[order end] then

(if DATENAME('weekday', [Order Date]) != 'Sunday' and DATENAME('weekday',

[Order Date]) != 'Saturday'

then

[Order Date] end ) end

Here order start & order end are the dates which are filtering the Order Date means Here are only those order dates which you can choose from order start & order end parameter & only weekdays date not all. Here you can choose order end as today with today() calculation.

Warm Regards,

Prashant Sharma - India | LinkedIn