Need Help with a formula to calculate a date

[Jerry Gray]

I am tracking benefit hours for each week, pay period (2 weeks) and quarter. My problem is that the quarter is not standard. The benefits quarter alternates a 14 week period with a 12 week period. I can only assume the use of alternating weeks is to keep the quarter ending on a pay period date, probably left over from manual calculations.

I am currently using a table lookup to get the quarter ending date but I would like to just create a field rule which calculates the date.

This year the problem is further complicated by a full pay period is left in the year after the quarter ends. In this case, an extra pay period (2 weeks) will be added to the quarter to adjust. This means that the 4th quarter will actually be another 14 week period. There will be three consecutive quarters of 14 weeks then quarters will revert to the 14-12 alternating time periods.

Quarters for 2008
Qtr 1 12/17/2007 to 03/23/2008 14 weeks (98 days)
Qtr 2 03/24/2008 to 06/15/2008 12 weeks (84 days)
Qtr 3 06/16/2008 to 09/21/2008 14 weeks (98 days)
Qtr 4 09/22/2008 to 12/14/2008 12 weeks (84 days) adjusted to end 12/28/2008 14 weeks (98 days)

Quarters for 2009
Qtr 1 12/29/2008 to 04/05/2009 14 weeks
Qtr 2 04/06/2009 to 06/28/2009 12 weeks
Qtr 3 06/29/2009 to 10/04/2009 14 weeks
Qtr 4 10/05/2009 to 12/27/2009 12 weeks

I need to be able to calculate the Quarter ending date, given any date. I currently calculate the Week_Ending date to search by key.
In other places, I calculate the Pay_Period ending date, again to do a search by key.
In the following formulae I use 1/7/1990 as a known Pay_Period ending date.

DIM my_date AS D
my_date = {11/17/2008}
'Calculate Sunday date
?{1/7/1990}+(ROUND_UP((my_date-{1/7/1990})/7,0)*7)
= {11/23/2008}
'or
?jtodate(7-(dow_iso(my_date))+ jdate(my_date))
= {11/23/2008}

'Calculate Pay Period Ending date
?{1/7/1990}+(ROUND_UP((my_date-{1/7/1990})/14,0)*14)
= {11/30/2008}

Does anyone have any ideas that will get me pointed in the right direction?

Thanks in advance,
Jerry Gray

[Tom Cone Jr]

Re: Need Help with a formula to calculate a date

Jerry, if you're looking for a formula that can compute beginning and ending dates for benefit quarters in any arbitrary year, this will not be possible unless the pattern for computing beginning and ending dates for benefit quarters is capable of being defined rigorously. What are the "rules" for computing these dates?

[Jerry Gray]

Re: Need Help with a formula to calculate a date

Tom,

Ignoring the adjustment mentioned, the 4 quarters total 364 days, starting the day after the end of the last quarter of the previous year. Qtr_1 contains 98 days, Qtr_2 contains 84 days, Qtr_3 contains 98 days and Qtr_4 contains 84 days. Since the benefit year only consists of 364 days, the extra day in the year forces the dates to creep.

The following works. There is probably a better way to do it but I can't see it right now. The Known_Qtr_End can be adjusted manually (maybe a global variable) which would allow for this year's adjustment for future dates. The adjustment won't be needed again for about 11 years.

DIM my_date AS D
DIM Known_Qtr_End as D
DIM Qtr_1 as D
DIM Qtr_2 as D
DIM Qtr_3 as D
DIM Qtr_4 as D
DIM Qtr_Ends as D
Known_Qtr_End = {1/7/1990}
My_date = {12/1/2008}

Qtr_4 = {1/7/1990}+(ROUND_UP((my_date-{1/7/1990})/364,0)*364)
Qtr_3 = Qtr_4 - 84
Qtr_2 = Qtr_4 - 182
Qtr_1 = Qtr_4 - 266
Qtr_Ends = Case(my_date<=Qtr_1,Qtr_1,my_date<=Qtr_2,Qtr_2,my_date<=Qtr_3,Qtr_3,my_date<=Qtr_4,Qtr_4)
?qtr_ends
= {12/14/2008}

Thanks,
Jerry Gray

[Tom Cone Jr]

Re: Need Help with a formula to calculate a date

Jerry, I wonder if something like this might work.

1) store your "My_date" in a global variable each time the application starts

2) Move your script into a UDF, with a single "date type" argument

3) Use a calc field rule expression that calls the UDF and passes the My_date variable to the function

Don't you suppose that would do the trick?

FUNCTION Qtr_Ends AS D (Curr_Date AS D )
	'DIM my_date AS D
	'DIM Known_Qtr_End as D
	DIM Qtr_1 as D
	DIM Qtr_2 as D
	DIM Qtr_3 as D
	DIM Qtr_4 as D
	'DIM Qtr_Ends as D
	'Known_Qtr_End = {1/7/1990}
	'My_date = Curr_date
	
	Qtr_4 = {1/7/1990}+(ROUND_UP(([COLOR="Red"]Curr_Date[/COLOR]-{1/7/1990})/364,0)*364)
	Qtr_3 = Qtr_4 - 84
	Qtr_2 = Qtr_4 - 182
	Qtr_1 = Qtr_4 - 266
	Qtr_Ends = Case([COLOR="red"]curr_date[/COLOR]<=Qtr_1,Qtr_1,[COLOR="red"]curr_date[/COLOR]<=Qtr_2,Qtr_2,[COLOR="red"]curr_date[/COLOR]<=Qtr_3,Qtr_3,[COLOR="red"]curr_date[/COLOR]<=Qtr_4,Qtr_4)

END FUNCTION

?qtr_ends({12/08/2008})
= {12/14/2008}

[Jerry Gray]

Re: Need Help with a formula to calculate a date

Tom,

I think you are right on the money with the UDF.

My_Date will actually be input from a form to summarize benefit hours for the quarter for reporting purposes. At our casino, employees must work an average of 35 hours per week for the quarter to maintain their company paid benefits. The first time their average drops below 35, they are put on probation until the end of the next quarter. If their average is below 35 for 2 consecutive quarters, they become part time employees and lose their benefits.

I will add a test to determine if Qtr_4 occurs 14 days or more before the last Pay Period ending date of the year. If the test is true, I will adjust the Known Qtr.

Thanks for your help,
Jerry Gray

[G Gabriel]

Re: Need Help with a formula to calculate a date

Here is a simple formula for you.

dim q1_Beging as d={12/29/2008}
Q1_end=addmonths(q1_Beging,3)-dow(addmonths(q1_Beging,3))+1
?q1_end
= {03/29/2009}
?cdow(q1_end)
= "Sunday"

q2_Beging=q1_end+1
q2_end=addmonths(q2_Beging,3)-dow(addmonths(q2_Beging,3))+1
?q2_end
= {06/28/2009}
?cdow(q2_end)
= "Sunday"
..and so on..