Recurring Dates -last and next

[DozAdmin]

I need a formula to display a recrruing date, the next one an the previous one from currentdate. The Day is a numeric value in a db field {BillDate}. I have the following formula - so far - which gives me the next {BillDate}:

###############
Local numbervar m:=month(CurrentDate)+1;

Local numbervar y:=year(CurrentDate);

Local numbervar d:=day(CurrentDate);

If m>12 then (m:=m-12; y:=y + 1);
################

In the same data some billdates are quarterly, which will have to deal with "last day of the month" issue.
Date(y,m,{PLANEESTAT.REBDAYNUM})

 

[lbass]

You could use:

dateadd("m",-1,currentdate) //or use billdate instead of currentdate

For the next, use:
dateadd("m",1,currentdate) //or use billdate instead of currentdate

If you use currentdate, the next billdate will change each day. Note that dateadd just changes the monthnumber--it doesn't accommodate different month ends. Do you want the billdates always to reflect the month end dates?

What do you want to see for previous quarter/next quarter? A date? If so, do you want the beginning of the quarter date or the end? Or?

-LB

 

[DozAdmin]

Thanks for the reply LB,

The filed billdate is a number, it is the day of the month only. for monthly recurring I can use this as I posted, except for those with 29, 30, 31 which I'll need to do some kind of last day of the month deal. I have the monthly recurring working. I'm now working on the annual recurring.

I have the fields; billdate - which is the day of the month for recurrance, I also have firstbilldate, I can use this for annual recurring but I need to figure out the formula for determining if currentdate is after the month and day in the firstbilldate.

 

[DozAdmin]

Sorry I don't work with Crystal often enough to remember, well much at all

I need to know how to calcluate a recurring billing date quarterly, semi-annually and annually.

In the report I need to display the next and previous billing date.

The data I have to work with this is {firstbilldate}, I also have a field [edited for clarity]- {billday} a numeric field which is the day of the month for billing.
I do think that the day in {billday} and {firstbilldate} will be the same for all records.

 

[DozAdmin]

Sample Data desired:
Name Freq Start Date LastBillDate NextBillDate
Jim M 7/1/08 2/1/09 3/1/09
Mary Q 9/30/07 12/31/08 3/31/09
Sally A 6/15/07 6/15/08 6/15/09
Carl S/A 4/13/06 10/13/08 4/13/09

 

[lbass]

Please show samples of billday, billdate, and firstbilldate and confirm the datatype of each.

-LB

 

[DozAdmin]

sample data:Start Date in previous sample is firstbilldate. Billday and Billdate are the same, (just misstyped in my post, didn't want to confuse so I didn't change it, it is a number, not a date, it represents the day.
Name Freq billday firstbilldate Previous Next
Jim M 1 7/1/08 2/1/09 3/1/09
Mary Q 31 9/30/07 12/31/08 3/31/09
Sally A 15 6/15/07 6/15/08 6/15/09
Carl S-Annual 13 4/13/06 10/13/08 4/13/09

In this sample Freq, billday and firstbilldate are fields in the db, and Name. What I need to provide is Previous and Next.

 

[lbass]

I started working on this, but it seems overly complicated to have to work with a theoretical last bill date. Don't you have a field that documents the actual bill date on on ongoing basis (not just the first billdate) so that it can be referenced to determine previous/next?

-LB

 

[DozAdmin]

I agree completely!! That's exactly what I said.

The Nextbilldate shows up in the program but, so far as I can tell, it is not a field in the db. According the the software vendor the date is not in the db.

Is there a date function that compares currentdate to the day/month of the firstdate? I was thinking this could be done and from there the Next and Previous dates could be determined.

 

[DozAdmin]

Can you tell me what is wrong with this?
If month({firstbilldate}) = 2 and day({firstbilldate})=29

Then Date(Year(CurrentDate),3,1) –1 else

Else Date(year(CurrentDate),month({firstbilldate}),day({firstbilldate})

 

[lbass]

What is the purpose of this report? If the previous bill date is just theoretical, what is the point? It doesn't mean anything.

-LB

 

[DozAdmin]

It isn't theoretical it is for accounting - I din't ask why they wanted it.
I've made some progress, I have a 2 formulas that look at the billfreq, 1 is for next, 1 for prev. @Next and @Prev.
@Next will point to the appropriate formula for that freq.

if billfreq="M" then @NextM else
if billfreq="A" then @NextA else
etc, etc
@NextM is...
Local numbervar m:=month(CurrentDate)+1;

Local numbervar y:=year(CurrentDate);

Local numbervar d:=day(CurrentDate);

If m>12 then (m:=m-12; y:=y + 1);

if {billday}<d then

Date(y,m,{billday}) else
Date(y,m-1,{@RebDay})

@NextA is...
if date(year(CurrentDate),month({FirstbillDATE}),day({firstbillDATE})) > CurrentDate
then
date(year(CurrentDate)+1,month({firstbillDATE}),day({firstbillDATE}))
else
date(year(CurrentDate),month({firstbillDATE}),day({firstbillDATE}))

But for semiannual and quarterly it will be more complicated!! I guess I will need to set a variable for the period in each. ??? I don't know much about setting variables in crystal formulas.