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.

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?

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.

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

Take your pick.

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

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

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.

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.