Month problem in formula

[afelya]

I am trying to enter a monthly amonth in each month up to 60 months (5years) depending on award dates which also depends on duration (1, 2, 3, 4, 5 years)

Local NumberVar x;

if {BID_SUBJ.AWARD_EXP_DATE} in [Date (2003, 02, 01) to Date (2003, 02, 28)]
then x := {@MonthlyRev}

else
if month({BID_SUBJ.AWARD_EXP_DATE}) > 0 and
month({BID_SUBJ.AWARD_EXP_DATE}) <= {BID_SUBJ.DURATION} * 12

then x := {@MonthlyRev}
else 0

I have 61 seperate formula created per month, then i am entering amounths on each cell if the award date is month prior to cell month i am on. Per if i am on 15th cell corresponds with march of 2003 then i will see an amounth in this cell, or if award date is couple months prior such as 2002 dec and the duration is 1 year for this project then i will see this amouth on 13th cell (jan2003) and (dur*12) 12 cells there after. I am having a hard time showing the amounths in the cells to follow during the dur*12 mounths of cells. My formula do not work for the second if statetment since it is always going to retun tru and put the amounth in the cell, any help will be greatly appriciated...

 

[kenhamady]

Use separate formulas to calculate the start and end dates based on the duration. Then check which months are within that range.

Do you store the Expiration date and the duration?
What version of CR. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[afelya]

It is version 8.5.

on the report i do not show exp date but yes i do store it on a suppressed report footer duration is there as well.

How can i set up a formula with start and end dates based on duration when duration depends on *12 to count the months and award date changes for each project?

 

[kenhamady]

If you add the duration to the award date don't you get the expiration date? Maybe I don't understand you completely.

But, if you have an award date and a duration in years, try the following formula for end date:

DateAdd(&quot;yyyy&quot;, {Duration}, {AwardDate})

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[afelya]

Seems like

@dur = dateAdd(&quot;yyyy&quot;, ({BID_SUBJ.DURATION}), ({BID_SUBJ.AWARD_EXP_DATE} ))

is doing the trick BUT say i used this in conjunction with my formula

Local NumberVar x;

if {BID_SUBJ.AWARD_EXP_DATE} in [Date (2003, 02, 01) to Date (2003, 02, 28)]
then x := {@MonthlyRev}

else
if (Date (2003, 02, 01)
//to Date (2003, 02, 28))
in
[{BID_SUBJ.AWARD_EXP_DATE} to {@dur}])
then x := {@MonthlyRev}
else 0

Is there a way to specify on the dateadd function to count the months and the years only without counting the dayes. If i use a award date of 02/05/02 with 1 year duration then the dateadd returns 2/5/2003, it is good but i need to point out the month not the dates. This ignores rest of the month after the 5th. I need result to cover the whole month of 2003/02 not only the first 5 days, because award date is 2/5/02.

Also in the actual formula i like to say if the month of februrary between award date and the formula date thenshow amounth, but i cant seem to figure out how to set up to point out the exact month. So above you will she the date range of first day of feb for testing purpose. But i need to be able to say for the month of such as february, any ideas?

 

[kenhamady]

If you use the first of the month, it should work (and you don't need really variables for this) :


If Date(2002,2,1)
in {BID_SUBJ.AWARD_EXP_DATE} to {@dur}
then {@MonthlyRev}
else 0 Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[afelya]

Thnaks but i can not hard code award dates, i wish i could. There is a different date for each project, so it changes...

else
if {@Marchmonth} <-- i need to be able to set this formula to point out the correct date of say the cell that points to month march of 2003
in [ ({BID_SUBJ.AWARD_EXP_DATE}) to {@dur}]

then x := {@MonthlyRev}
else 0

 

[kenhamady]

My formula wasn't to hard code the award date, it was to hard code the February, 2002 column of the report. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[afelya]

Well I was way off on my thinking, now i see clearly what you mean. I was making it alot harder then it was. Thnak you so very much...