cancel
Showing results for 
Search instead for 
Did you mean: 

Help with date formula

Former Member
0 Kudos


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}

Accepted Solutions (1)

Accepted Solutions (1)

former_member292966
Active Contributor
0 Kudos

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

Former Member
0 Kudos

That worked perfectly!

Thank you!

Answers (0)