Excluding a particular month with DateDiff 1

[martinjt]

Hi - I have CR9 with Oracle. Trying to do a month version of the following formula example that calculates the number of days between the order date and ship date, excluding Saturdays and Sundays:

Local DateTimeVar d1 := {Orders.Order Date};
Local DateTimeVar d2 := {Orders.Ship Date};
DateDiff ("d", d1, d2) -
DateDiff ("ww", d1, d2, crSaturday) -
DateDiff ("ww", d1, d2, crSunday)

I need to count months instead ("m" with DateDiff) between two dates and exclude all Augusts. Is there a way?

 

[lbass]

Not sure whether you want to exclude August if the end date is somewhere in the month of August, of only if the entire month is in the range. The following assumes you are omitting August only if the entire month is included:

whileprintingrecords;
datevar st := date({table.startdatetime});
datevar end := date({table.enddatetime});
numbervar aug := 0;
numbervar cnt := 0;
numbervar diff := datediff("m",st,end);
for cnt := 0 to diff do(
if month(dateserial(year(st), month(st)+cnt,1)) = 9 then
aug := aug + 1
);
diff-aug

-LB

 

[martinjt]

The end date is always the last day of the month so yes, the entire month calculation you gave is perfect and it worked beautifully. THANKS!

 

[martinjt]

LB,
I want to double check something. Did you mean to put 8 for this line:

if month(dateserial(year(st), month(st)+cnt,1)) = 9 then

or is 9 correct? I believe the following line is the clue but making sure I've got it right.

 

[lbass]

I used 9 (for Sept) in order to require that August has passed before counting it as a month to omit.

-LB

 

[martinjt]

Makes sense. It works fine in one report but I didn't get the results I expected in another one which tells me I need to reconsider what's going on with the other report! Thanks.