This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
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.
@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.
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.
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).
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).