Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Help with date formula


I am using this formula in another report which is working great.  However, I would like to modify it so that if I run it on Monday, it pulls the starting date of "next Monday" and includes that week.

Example:  I run the report on 9/22.  I would like the dates in the report to include only this week:  9/29 - 10/3.

What do I need to change in the following code to get this to work?

Thanks.

1.  Create a field formula that I'll call {@StartDate}



NumberVar dow := DatePart('w', CurrentDate, crMonday) - 1;  //Day of week, starting on Monday.


NumberVar days := 7 - dow;


CurrentDate + days



2. Create a field formula that I'll call {@EndDate}:



{@StartDate} + 7



3.  In the Select Expert, manually edit the selection formula to use these date formulas instead of the parameters.



{YourDateField} >= {@@StartDate} and  {YourDateField} <={@@EndDate}

Former Member
replied

Hi,

Instead of using 3 separate formulas, bring them all into the Record Selection formula like:

NumberVar dow := DatePart('w', CurrentDate, crMonday) - 1;  //Day of week, starting on Monday.
NumberVar days := 14 - dow;

DateVar StartDate := CurrentDate + days;

DateVar EndDate := StartDate + 4;

{YourDateField} >= StartDate and {YourDateField} <= EndDate;

I changed the days variable to use 14 because you want the following week and not the next one.  EndDate will take you to the Friday of that week as well.

Having the separate formulas may cause problems depending on when Crystal evaluates each formula.  Doing it all together eliminates that issue.

Good luck,

Brian

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question