phone: (540)338-0194
email: ken@kenhamady.com

´╗┐

 Crystal Reports formula examples

How to Find the Last Friday of a Month (or any other day of the week):

The following formula will allow you to find the last Friday of any month, based on an input date that you provide.  Your input date can be a database field, a formula, a parameter field or even the function CurrentDate.  The Input Date goes on the second line, in place of the field Order Date.   You can also change the formula to find another day of the week by changing the number 6 to any number from 1 to 7.  Use 1 for Sunday and 7 for Saturday, etc.

Local DateVar DIn:= {Orders.Order Date};   //Your date field/parameter or CurrentDate
Local NumberVar DOW:= 6;    // Your target DayOfWeek - any number from 1 to 7, with 1 for Sunday

DIn := (DIn - day(DIn) + 32); // Find a date in the next month
DIn := DIn - day(DIn);        // Go to the last day of the original month

if (DayOfWeek(DIn) < DOW )
then DIn := DIn - 7;
DIn - DayOfWeek(DIn) + DOW


(Thanks to
Roberto Brum from Brazil for correcting and simplifying my original formula)