Determining Sunday (1st Day of the Week)

[tmozer]

I want to create a report that evaulates data collected from Sunday of the current week. If I call whatever date Sunday is StartWeek, I need to select data between StartWeek and currentdate.

I tried the following formula with no success:

shared dateVar sdate := currentdate;
if weekday(currentdate) = 2 then sdate = (currentdate - 1) else
if weekday(currentdate) = 3 then sdate = (currentdate - 2) else
if weekday(currentdate) = 4 then sdate = (currentdate - 3) else
if weekday(currentdate) = 5 then sdate = (currentdate - 4) else
if weekday(currentdate) = 6 then sdate = (currentdate - 5) else
if weekday(currentdate) = 7 then sdate = (currentdate - 6)

I'm sure there is a much better formula that actually works..... I am using Crystal Reports 8.5.

 

[lbass]

You could use the weektodatefromsun function, as in the record selection formula:

{table.date} in weektodatefromsun

This is the equivalent of:

{table.date} in currentdate-dayofweek(currentdate)+1 to currentdate

-LB

 

[tmozer]

I tried to run my report with your selection formula:

{LABEXAM.Section} in ["CRIM", "DNA", "DRUG", "TOX"] and
{LABEXAM.Date Assigned} in WeekToDateFromSun

and got a blank report. But then, duh, I realized, it's Sunday today! I want to add the logic that if it is Sunday, the data is collected for the previous work week (Sunday through Saturday). Any suggestions for that??

BTW, were can I find out about all these nifty date functions? I don't see them on my CR 8.5 formula window list.....

 

[tmozer]

Now my administration tells me.....

They want the week to start at 12:01 am Monday. Therefore I need to collect my data from the Monday of the current week of the run to the current date. So if I ran the report today it would collect all the data from Monday through today (Sunday).

So, obviously, my selection formula needs to be Monday of the currentdate to currentdate.

I have not tried it, but is there a weektodatefrommon function??

 

[lbass]

Use:

{table.date} in currentdate-dayofweek(currentdate,crMonday)+1 to currentdate

If you don't want to include the currentdate, then use:

{table.date} in currentdate-dayofweek(currentdate,crMonday)+1 to currentdate-1

-LB