# Date calculation that excludes weekends

This topic is 6641 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

• Newbies

I have been trying to create a calculation date fields that excludes weekends and this may sound crazy but the calculation keeps ignoring me. No really I tried a simple case statement using DayofWeek function (Trade Date 5 = DayOfWeek (1); Trade Date Day (6): and it just added the days up as if there were no rule giving me a "settlement day of exactly 5 days from the trade date.

Any help would be appriciated - dates always seem to throw me for a loop so to speak.

##### Share on other sites

Hi Titus,

Your function doesn't make sense to me. Maybe you could say more about what you are trying to do. Is this to find the number of weekdays between two dates? Or show the date after X number of weekdays? Something else?

##### Share on other sites

• Newbies

I;m sorry if I wasn't claer I was just trying to return a "settlement date" that was 5 days from the "order date" but if the "settelment date landed on a Saturday or Sunday I wanted it to skip ahead to Monday.

##### Share on other sites

Let( dow = DayOfWeek( orderdate ); orderdate + 5 + Case( dow = 2; 2; dow = 3; 1 ) )

or

Let( dow = DayOfWeek( orderdate ); orderdate + 5 + (dow = 2) * 2 + (dow = 3) )

##### Share on other sites

Can't test your 7 formula....

Staying with 6 for a while

(date+5)+Choose(7-DayOfWeek(date+4),1,2)

Hmm.. not sure what "from" means exactly

##### Share on other sites

Choose won't work in this instance unless the days are Monday or Tuesday. You would need to use Choose( 7-DayOfWeek(orderdate+4); 1; 2; 0; 0; 0; 0; 0 ).

##### Share on other sites

Hi JT,

Then I must have misunderstood the point here...because it works if the goal is to postpone to the next Monday in case date +5 falls on Saturday or Sunday.

Even if there would be an easier calc for this instance I'm sure.

##### Share on other sites

You are correct. However, in 7, the Choose function seems to produce weird pattern results, if you haven't specified what the result should be if the test equals a specific number. In this example, June 1st works, while June 2nd through June 6th produces June 13th, June 7th and 8th produce June 14th; then it jumps to June 20th for the 9th through the 13th. Once I added the zeroes, it worked fine.

The odd thing is that the Choose by itself produces no visible result when the test doesn't equal zero or one. It's only when added to orderdate or 5 that it gets screwy.

##### Share on other sites

This topic is 6641 days old. Please don't post here. Open a new topic instead.

## Create an account or sign in to comment

You need to be a member in order to leave a comment

## Create an account

Sign up for a new account in our community. It's easy!

Register a new account