DateTimeAdd to Only Result in a Business Day

[Brian32]

Thank you for looking into my inquiry.

I have an IF Statement that, based on varioius criteria, will subtract a specific number of days, respecitively, from a date column.  I don't want to only subtract Business Days.  All days should be accounted for in the subtraction, but what I need to have happen is that if the resulting date lands on a holiday or weekend I need it to go to the next business day.

Any ideas??

[JohnJPS]

If you have data that specifies date along with wether or not that date is a business day; you could dynamically query it with a where clause that grabs the max business day less than or equal to the day you pass in.  So you do your calculation and pass the result to the dynamic query.  If was already a business day, it will match as being equal to a business day in your data; if not, the "max less than" should return the correct day you're after.

Would that work for you?

[Brian32]

It sounds like it would.  I am just not sure how to do what you are describing.

[KaneG · Accepted solution]

Hi @Brian32,

You can see what day a date is with '%a' (%A for full text) and so there is a couple of ways to do this. All in one formula would be something like this:

IF DateTimeFormat(DateTimeAdd([Date],-50,'days'),'%a')=='Sat'
THEN DateTimeAdd([Date],-50+2,'days')
ELSEIF DateTimeFormat(DateTimeAdd([Date],-50,'days'),'%a')=='Sun'
THEN DateTimeAdd([Date],-50+1,'days')
ELSE DateTimeAdd([Date],-50,'days')
ENDIF

You could make certain elements of this formula into their own variables naturally to make the formula more dyanmic.

Kane

[Brian32]

Oh I see.  That makes sense.  Thank you!

[JohnJPS]

@KaneG, I like the nice, concise approach, however a word of caution that while it avoids weekends, it could still land on a holiday.  That's why I was wondering about @Brian32 having another dataset that told where the holidays were.  Just a thought.

[KaneG]

To counter for holidays, you would need a lookup table with the holidays listed as they are different across states and countries. There is a few discussions on these forums about that including one from about last September that had about 20 comments and about 4-5 different macros/methods/solutions. Try searching for business days or networkdays... I'll have a look when I next air down at my computer.

[JohnJPS]

Here is a comment thread talking about finding the nearest value from a lookup table; it could perhaps be modified to solve our problem of finding ("looking up") the nearest business day less than or equal to a given day: https://community.alteryx.com/t5/Data-Preparation-Blending/Select-nearest-value-from-look-up-table/m....

Assuming we can limit the date table to a fairly small range (e.g. last year or even last 90 days or some such), then we wouldn't need to utilize the R approach suggested thereing, which performed better on larger data).

[KaneG]

Just so that it's linked... here are some discussions on similar including several macros that people created for different incarnations of Business day with/without holidays and to get different calculations (date, or distance etc).

- http://community.alteryx.com/t5/Data-Preparation-Blending/Datetimediff-using-only-weekdays-business-...

- http://community.alteryx.com/t5/Data-Preparation-Blending/DateTimeDiff/m-p/3732/highlight/true#M582

Kane

[Paresh1pandya]

Hello ,

I am looking to get todays date and next business date data only from my entire data , I am not sure, how to achieve as I have some more data for previous year and future years,

Appreciate your help on this.