DateTimeAdd to Only Result in a Business Day

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??

Date Time

Accepted answers

KaneG

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

All comments

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

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:

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

Kane

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-p/12412/highlight/true#M2261.

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).

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.

JohnJPS

@Paresh1pandya ,

I'm not sure of the details of your issue. It sounds as though you are matching dates where the year is incorrect? If so, you would need to add year information to your data. If you do have year information then the year part of DateTimeToday() should allow you to filter accordingly.

If your issue is something else entirely, please elucidate.

Hope that helps!

John

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703