Determine prev. Sat. date from a datetime field (CR 8.5)

[OverDraft]

Is there a function (or combination of functions) that I can use in Crystal Reports 8.5 that will enable me to display the previous Saturday's date based on a value in a date/time field?

For example, if my incomming records all have a date/time value of say, Wed. 7/20/2005 (but it could have been any weekday during that week) - I want to display the previous Sat. (7/16/2005) in the report header.

(The report includes some timekeeping data which corresponds to pay period end dates that always fall on every other Saturday. But I don't have the end dates themselves. All I have is the date the data was extracted which typically falls in the middle of the week following the close of the pay period.)

I've tried looking through the Crystal online help for functions (and a bit of searching around here) but I don't see any solution that addresses this specifically.

Thanks in advance.

 

[TheBlondOne]

Hi there,

You could use something like the following formula.

if dayofweek({DATE FIELD}-1) = 7 then display date else
if dayofweek({DATE FIELD}-2) = 7 then display date else
if dayofweek({DATE FIELD}-3) = 7 then display date etc....

What this will do is extract the day from the date and then convert this into a number i.e 1-7. The 1-7 numbers reflect the day of the week i.e

1 = Sunday and 7 = Saturday

When this formula returns a true value it will then display the date which should be the previous Saturday's date.
HTH

 

[TheBlondOne]

Sorry I missed something out.

The formula should be like this

if dayofweek({DATE FIELD}-1) = 7 then {DATE FIELD} -1 else
if dayofweek({DATE FIELD}-2) = 7 then {DATE FIELD} -2 else
if dayofweek({DATE FIELD}-3) = 7 then {DATE FIELD} -3 etc.

Thanks

 

[bdreed35]

You could simplify it down to this:

{table.date} - (dayofweek({table.date},crSaturday)-1)

~Brian

 

[OverDraft]

Thanks a bunch. I tried them and both solutions work equally well.