Show week number across multiple years.

[dz0k52]

I am using Crystal 8.5 with an Oracle 9.0 database.I am trying to show the "week #" of a date in which a task has been scheduled. I have found multiple ways of doing this but I am struggling with dates that span previous or subsquent years. For example a task scheduled for January 13th 2003 shows up as week 2 but because of my start date, a task scheduled for October 12 2005 shows up as week -11. How can I use my datediff function to look at Jan 1st of the year which the task was scheduled so that the October 12 2005 task shows up as week 41? My formula looks like this:
DateDiff ( 'ww' , #01/01/2006# -1, {WORKORDER.TARGSTARTDATE} , 1 )

 

[lbass]

Not sure I follow. To find the difference in weeks between the start date and the task date, you would use:

datediff("ww", {table.startdate},{table.taskdate})
//where the most recent date is the second date in the formula

If you want the week number to reflect the week of the year, then use:

datediff("ww",date(year({table.scheduleddate}),01,01),{table.taskdate})

-LB

 

[dz0k52]

Actually you did follow, perfectly. Could you explain a little about the function you used like what does the 01,01 do, I would really appreciate it.

 

[lbass]

The part of the datediff formula that you are looking at:

date(year({table.scheduleddate}),01,01)

...is a date, which takes the year from the schedule date, and then adds 01,01 as the month and day arguments of the date. You don't even really need the zeros.

-LB

 

[dz0k52]

Thanks again it worked excellent.