Jump to content

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.

Link to comment
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?

Link to comment
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.

Link to comment
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) )

Take your pick. yay.gif

Link to comment
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 ).

Link to comment
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.

Link to comment
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. ooo.gif

Link to comment
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

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.