Creating formula to calculate number of days??? 2

[Apollo6]

I need to determine the number of days in the past three months (whole months). For example, if I ran the report today, it would not look at March but start with February, then January, then December. The number returned would be 90.

I have a formula in Excel that gives me the results but it uses Excel Functions.

=DAY(EOMONTH(TODAY(),-1))+DAY(EOMONTH(TODAY(),-2))+DAY(EOMONTH(TODAY(),-3))

Are there functions in Crystal that can help me write the same formula?

I'm using CR v8.5.

Thanks in advance for any suggestions.

 

[lawlerpat]

Apollo6,

There a quite a few good functions in Crystal's Formula Editor under the Functions category.
You can do somthing like the following

local datetimevar startDay;
Local datetimevar endDay;
Local numberVar DayCount;

endDay:=Date(Year({?MonthStart}), Month({?MonthStart}), 01);
endDay:= dateAdd("d", -1, endDay);

startDay:= Date(Year({?MonthStart}), Month({?MonthStart}), 01);
startDay:= DateAdd("m", -3, startDay);

DayCount:= dateDiff("d", startDay, endDay);

DayCount;

 

[Apollo6]

lawlerpat,

Thanks for the help. I managed to get the correct answer but had to do it using three parameters. Yours is much cleaner and easy to use for the end-user.

Thanks again!

 

[Apollo6]

Just a follow-up question:

I replaced {?MonthStart} with (CurrentDate-100). This report will be ran and the start of every month. So, CurrentDate-100 will always put me into the month that is three months ago.

This allows me to remove the parameter and take the user out of the process for determining the day count. Do you see any potential issues with doing it this way?


>>Modified Formula Box<<

local datetimevar startDay;
Local datetimevar endDay;
Local numberVar DayCount;

endDay:=Date(Year(CurrentDate-100), Month(CurrentDate-100), 01);
endDay:= dateAdd(&quot;d&quot;, -1, endDay);

startDay:= Date(Year(CurrentDate-100), Month(CurrentDate-100), 01);
startDay:= DateAdd(&quot;m&quot;, -3, startDay);

DayCount:= dateDiff(&quot;d&quot;, startDay, endDay);

DayCount;

 

[synapsevampire]

There are a few problems with this approach to record selection, the most important being that Crystal will not pass the SQL to the database if a variable is used to construct the Record Selection criteria.

If you want a date which is the 1st for 3 months ago, try:

dateadd(&quot;m&quot;,-3,currentdate-(day(currentdate)-1))

Use the same approach to any other dates required.

And DON'T use any variables, just this formula.

-k

 

[Apollo6]

synapsevampire-

I tried your formula and it does return 12/1/02 but that's not really what I'm looking for...

I am not using the results of the '@Days' formula to select specific records, more so, I am executing the SQL to bring back a total dollar amount of records with their own criteria from the database.

Once I get the total dollar amount, I need to divide that total by the number of days the '@Days' formula came up with. The '@Days' formula would not be going against the database at all, just using date/time functions.

With that being the case, do you see anything wrong with going about it the way I described above? Or likewise, in Excel, it's a single line formula, would that be possible in Crystal?

Thanks for the feedback.

 

[Apollo6]

One line answer:

DateDiff (&quot;d&quot;,dateadd(&quot;m&quot;,-3,currentdate-(day(currentdate)-1)),currentdate-(day(currentdate)-1))

A star for helping me in the right direction.

 

[synapsevampire]

Not sure what: &quot;I am executing the SQL to bring back a total dollar amount of records with their own criteria from the database&quot; means.

What is this &quot;own criteria&quot;?

If you provide sample data and expected output, someone will probably field your questions more readily.

It sounds like you want the sum of some field starting from 3 months ago, which means that limiting the rows to just that data makes the task simpler and more efficient.

The eomonth simply returns an end of month date in Excel, which is fairly simple in Crystal:

End of month for 3 months ago:

dateadd(&quot;m&quot;,-2,currentdate-(day(currentdate)))

To limit rows in the report for a single month (3 months ago), use:

Report->Edit Selection Formula->Record and place:

{Table.field} >=
dateadd(&quot;m&quot;,-3,currentdate-(day(currentdate)-1))
and
{Table.field} <=
dateadd(&quot;m&quot;,-2,currentdate-(day(currentdate)))

Or if you want it to date, adjust the latter formula accordingly.

Now just place the field to sum in the details of your report and right click it and select insert summary->Sum

You can then delete the field in the details if need be.

There are also Running Totals for defining specific criteria for a summary, just place the appropriate code in the X-2 under Evaluate->Use a formula

-k

 

[Apollo6]

Sorry for not being clear...

My original situation:
&quot;I need to determine the number of days in the past three months (whole months).&quot;

I built a basic report to bring back a total dollar amount from multiple records in the database. Once I have that total, I need to do some calculations with @Days.

@Days is the number of days in the past three months (whole months).

@Days = DateDiff (&quot;d&quot;,dateadd(&quot;m&quot;,-3,currentdate-(day(currentdate)-1)),currentdate-(day(currentdate)-1))

The @Days formula is not passed as part of the criteria for the report. It is simply used to divide by once the report has completed. If ran today, @Days will return 90. This is the number of days in February, January, and December. If ran in April, it returns the number of days in March, February, and January.

I just used DateDiff with your formula to get the one-line formula to return the number of days:
-----------------
DateDiff (&quot;d&quot;,dateadd(&quot;m&quot;,-3,currentdate-(day(currentdate)-1)),currentdate-(day(currentdate)-1))
-----------------

Sorry for the confusion. You pointed me in the right direction with:

dateadd(&quot;m&quot;,-3,currentdate-(day(currentdate)-1))