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?
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.
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.
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.
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.
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);
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.
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.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.