Skip to Content
0
Former Member
Jun 02, 2011 at 03:53 AM

Use data from previous year

37 Views

Hi,

Am trying to figure out a formula/s to show me the date of the previous year, based on day of the week and week number.

Say yesterday is Wednesday 1st June. I want to find out the DATE for Wednesday LAST YEAR. I know that this date is 2/6/2010 by checking a calendar.

So what I want is for an automatic calculation based on the week number and then the day number.

So far I have the following (but have not figured out how to tie them together to get the date):

@previousyear

date(year(currentdatetime)-1,month(currentdatetime),day(currentdatetime)-1)

@weeknumber

DatePart ("ww", {@PreviousYear})

I tried this, which I got from Ken Harmady website, but it gives me the date of 8th of June 2010 and no matter what I do to it, I cannot get it to say 2nd June 2010:

WhileReadingRecords;DateVar target := Date (2010,1,1);

if DayOfWeek (target) in 2 to 5

then target:= target - 7 * ({@weeknumber}-1)

else target:= target + 7 * ({@weeknumber}) ;

target - DayOfWeek (target) + {@dayofweek}

I am using CR2008 & SQL database