Calculate future dates - first and third Thursdays

[mocgp]

Using BOXIR2; Oracle 10

Using a given Date field, is it possible to create a formula that will return the date of the next nearest first Thursday of the month or, if the given date field is already past the first Thursday, then the third Thursday?

 

[Wallie12]

How to Find the number of Fridays in a date range (or any other day of the week):
The following formula will allow you to count the number of Fridays in any date range. Your input dates can be database fields, formula fields or parameter fields. The Input Dates goes in place of {StartDate} and {EndDate} . You can change the formula to count another day of the week by changing the number 6 to any number from 1 to 7. Use 1 for Sunday and 7 for Saturday, etc.

Note - You should subtract one day from the start date if you want date ranges that start on Friday to include that first Friday in your count. In other words if an 8 day range from Friday to Friday should count 2 Fridays.


DateDiff ( 'ww' , {StartDate} - 1 , {EndDate} , 6 )

Alter to fit your needs

from:

http://www.kenhamady.com/form24.shtml

 

[mocgp]

Wallie12 -

Thanks for the response but I'm afraid that you missed the question entirely. I arrived at a solution a few days after posting the question. The required formula is -

DateVar dte:= Date({DateField});
NumberVar DayOfMnth:= Day(dte);
NumberVar DayOfWk:= DatePart("w",dte,crThursday);
NumberVar DaysInMnth:=
DateSerial(Year(dte),Month(dte)+1,1-1)-
DateSerial(Year(dte),Month(dte),1)+1;
NumberVar DaysLeft:= DaysinMnth-DayOfMnth;
NumberVar DaysToThur:= 7-DayOfWk+1;
NumberVar X:= DayOfMnth+DaysToThur;
NumberVar WkFactor:=
If (Day(dte) in [1 to 7] and X in 8 to 14 or
Day(dte) in [8 to 14] and X<15) then 2 else
If (Day(dte) in [8 to 14] and X in 15 to 21 or
Day(dte) in [15 to 21] and X<22) then 1 else
If((Day(dte) in [15 to 21] and X in 22 to 29 or
Day(dte) in [22 to 29] and X<29)) and
DaysLeft+DayOfWk>14 then 3 else
If (Day(dte) in [15 to 21] and X in 22 to 29 or
Day(dte) in [22 to 29] and X<29) then 2 else
If((Day(dte) in [22 to 29] and X in 29 to 35 or
Day(dte) in [29 to 31] and X<36)) and
DaysLeft+DayofWk>7 then 2 else
If (Day(dte) in [22 to 29] and X in 29 to 35 or
Day(dte) in [29 to 31] and X<36) then 1 else 1;

If DayOfWk = 1 and
(DayOfMnth in 1 to 7 or DayOfMnth in 15 to 21) then dte
else dte-(7-DaysToThur)+7*WkFactor

If you re-read the question carefully, you will see that it is a rather complex objective. If there is an easier way to do it, I would be glad to dump my formula in favor of it. Your response does not address the question in the least. The objective was not to return a number but actual future dates.

 

[IanWaterman]

This formula will do what you want.

@Thursday

global datevar first;
global datevar last;
global datevar start:=currentdate;
//global datevar start:=date(2010,6,20);

if day(start) > 7 then
first:= Date(year(dateadd("m", 1, start)), month(dateadd("m", 1, start)), 01)
else
first:= Date(year(start), month(start), 01);

last:= Date(year(dateadd("m", 1, start)), month(dateadd("m", 1, start)), 01)-1;


if currentdate > last - 7 then first - weekday(first) + 5
else
If first < start and start < first-weekday(first) + 19 then first-weekday(first) + 19 else
first-weekday(first) + 5;

You can test it by commenting out where Start = current date and use the manual setting for Start.

Ian

 

[mocgp]

Ian -

I plugged your formula into my query that hits a static date table and compared it to the results of my formula. Your formula does not match up; it seems to change the date on various days of the week instead of Friday (when it is supposed to switch to the next nearest first or third Thursday) and mostly returns the first Thursday only, ignoring the third Thursday.

For example, the date returned for today is 6/3. On 6/4 (Fri), that is supposed to change to 6/17. Your formula switches to 6/17 on Wednesday 6/2, then switches to 7/1 on Tuesday the 8th. That wasn't supposed to happen until Friday the 18th.

If you have a static date table, insert my formula and you'll see what I mean. I have tested my formula from 1/1/2009 to 12/31/2030 and it always returns the correct date for the next First or Third Thursday.

Thanks.

 

[IanWaterman]

Sorry I was just about to post again, that it did not work.

Going down this route, but not quite there yet, as it does not work for the date loaded.

global datevar first;
global datevar nextfirst;
//global datevar start:=currentdate;
global datevar start:=date(2010,5,13);


first:= Date(year(start), month(start), 01);

nextfirst:= Date(year(dateadd("m", 1, start)), month(dateadd("m", 1, start)), 01);


if start > first-weekday(first) + 19 then nextfirst - weekday(nextfirst) + 5
else
If Start > first-weekday(first) + 5 and start < first-weekday(first) + 19 then first-weekday(first) + 19 else
nextfirst-weekday(first) + 5;

//nextfirst
//first-weekday(first) + 19

 

[IanWaterman]

Think this work now. YOu will want to check results when date is third thursday, currently shows next first.

global datevar first;
global datevar nextfirst;
global datevar firstthur;
global datevar nextfirstthur;
//global datevar start:=currentdate;
global datevar start:=date(2010,6,17);


first:= Date(year(start), month(start), 01);
FirstThur:= first-weekday(first) + 5;

nextfirst:= Date(year(dateadd("m", 1, start)), month(dateadd("m", 1, start)), 01);
nextfirstthur:= nextfirst-weekday(nextfirst) + 5;

If firstthur < first then firstthur:= firstthur +7;
If nextfirstthur < nextfirst then nextfirstthur:= nextfirstthur +7;


if start < firstthur +14 then firstthur +14
else
If Start >= firstthur +14 then nextfirstthur;



Ian

 

[mocgp]

Still doesn't work right. I think what you are failing to take into account is that there can be 5 Thursdays in a month (ex., Apr, Jul, and Sep of this year). Your code lines up with mine up until the end of July then returns 8/19 on Aug 1 when it should be 8/5.

 

[IanWaterman]

I knew I had missed a test ;-)

Only need to change last part

if start > firstthur and start < firstthur +14 then firstthur +14
else
If Start >= firstthur +14 then nextfirstthur
else
firstthur;

Ian

 

[mocgp]

OK, that works except for one slight difference. My code changes the date on the Friday after the first and third Thursdays, your code changes the date on the the Friday after the first Thursday but then changes on the Third Thursday instead of the Friday after the third Thursday. If you can make that adjustement, I'll keep your formula as the preferred code.

Thanks for taking a look at this.

 

[IanWaterman]

I did say earlier that you needed to decide where the third thursday is triggered.

You will need to play around with position of the >= to get your desired result.

if start > firstthur and start < firstthur +14 then firstthur +14
else
If Start >= firstthur +14 then nextfirstthur
else
firstthur;


Ian