4 Replies Latest reply on Nov 18, 2013 8:35 AM by ultranix

# Conditional date of the week

### Title

Conditional date of the week

### Post

I would like to have a calculation, that, depending on date entered, would calculate the 2nd Monday of the given month with a condition (explained later). (two fields - date_entered, date_calculated)

1) So, if date_entered is 2013-11-05, it would return 2011-11-11,

2) if the date has already passed this month's 2nd monday, then it would return 2nd monday of next month. so if date_entered is 2013-11-18, date_calculated would return 2013-12-09 (2nd monday of next month).

• ###### 1. Re: Conditional date of the week

Let ( [ DT = YourDateFieldHere ;
M1st = DT - Day ( DT ) + 1 ;
Mon1 = M1st - DayofWeek ( M1st ) + 2 ; //Monday of 1st week
Mon2 = Mon1 + 7 + 7 * Mon1 < M1st ; //adjust 7 more days if Mon1 computes to be in last week of preceding month.
M1stb = Date ( Month (DT ) + 1 ; 1 ; Year ( DT ) ) ;
Mon1b = M1stb - DayofWeek ( M1stb ) + 2 ;
Mon2b = Mon1b + 7 + 7 * Mon1b < M1stb  //adjust 7 more days if Mon1b computes to be in last week of preceding month.
];
IF ( Mon2 < DT ; Mon2 ; Mon2b )
) // Let

That's one approach. A recursive custom function could do it more simply. I have not had the time to test this calculation, however, so it may not work or need some debugging.

• ###### 2. Re: Conditional date of the week

I tested it, it doesn't work quite correctly.

I checked all expressions, 2 doesn't seem to work (returns 0) - "Mon2" and "Mon2b".

• ###### 3. Re: Conditional date of the week

Had to fix three things (in red):

Let ( [ DT = YourDateFieldHere ;
M1st = DT - Day ( DT ) + 1 ;
Mon1 = M1st - DayofWeek ( M1st ) + 2 ; //Monday of 1st week
Mon2 = Mon1 + 7 + 7 * ( Mon1 < M1st ) ; //adjust 7 more days if Mon1 computes to be in last week of preceding month.
M1stb = Date ( Month (DT ) + 1 ; 1 ; Year ( DT ) ) ;
Mon1b = M1stb - DayofWeek ( M1stb ) + 2 ;
Mon2b = Mon1b + 7 + 7 * ( Mon1b < M1stb )  //adjust 7 more days if Mon1b computes to be in last week of preceding month.
];
IF ( Mon2 < DT ; Mon2b ; Mon2 )
) // Let

• ###### 4. Re: Conditional date of the week

Thank you, perfect as always, Phil!