cancel
Showing results for 
Search instead for 
Did you mean: 

Selection Formula, Need to get Date after a week ago Sunday.

Former Member
0 Kudos

Post Author: DaveChel

CA Forum: Formula

I need to create a selection formula which selects tasks that are not completed or were completed last week. So far I have:

isnull({TASKS.COMPLETED}) or

dateadd("d", 7,{TASKS.COMPLETED}) in weektodatefromsun

But I don't think that will work. In other words, no matter which day this week the report is run I want to pull Tasks.Completed (a date/time field) for values from the previous week Sunday to Monday.

I've been working on this myself for some time. Looked in the help section and searched this and another message board. Can anyone point me in the right direction?

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

Post Author: DaveChel

CA Forum: Formula

Thanks bettername. You've pointed me in the right direction and I will experiment with it today.

Former Member
0 Kudos

Post Author: bettername

CA Forum: Formula

Ah, that was just a snippet to get you on your way: You'll have to excude my Crystal syntax-ness - it's the way I've always done it! Try putting this code in a formula, and making the Select Expert say: {@YourFormula} = true.

Formula {@YourFormula}

datevar mydate:=currentdate;

datetimevar start_range;datetimevar end_range;

//date range is Sunday just gone-1 week) to (Sunday just gone).if weekdayname(weekday(mydate))="Sunday" then start_range := dateadd("ww",-1,mydate) else start_range := dateadd("ww",-1,dateadd("d",-weekday(mydate)+1, mydate));

end_range:= dateadd("d",8,start_range);

if isnull({TASKS.COMPLETED}) or {TASKS.COMPLETED} in start_range to end_range then true else false;

Former Member
0 Kudos

Post Author: DaveChel

CA Forum: Formula

Thanks for your help bettername.

I'm much better in VB than Crystal language although the selection formulas have to be in Crystal.

However, I'm working with Date/Time Fields. So, if I added:

{TASKS.COMPLETED} in totext(start_range,"dd/MM/yyyy") + " to " + totext(end_range,"dd/MM/yyyy");

This wouldn't work because your totext statement doesn't account for time? How would I edit it to fit?

Former Member
0 Kudos

Post Author: bettername

CA Forum: Formula

Does this help? I think I've got your date range selection right - for today (17th) it should show Sunday 6 Jan to Monday 14 Jan...

datevar mydate:=currentdate;

datetimevar start_range;datetimevar end_range;

//date range is Sunday just gone-1 week) to (Sunday just gone).if weekdayname(weekday(mydate))="Sunday" then start_range := dateadd("ww",-1,mydate) else start_range := dateadd("ww",-1,dateadd("d",-weekday(mydate)+1, mydate));

end_range:= dateadd("d",8,start_range);

totext(start_range,"dd/MM/yyyy") + " to " + totext(end_range,"dd/MM/yyyy");