Week format as weekending date

[#1]

Hi Guys

as part of my script I have the dates formatted as

Date([Date received]) as Date,

Month(Date([Date received])) as Month,

Week(Date([Date received])) as Week,

Year(Date([Date received])) as Year,

However rather than the Week format be as 1,2,3, as in week 1 week 2 week 3 etc

Is it possible to have it formatted as the last date of the weeks, so if the 1/1/2012 was a monday

Week 1 = 7/1/2012

Week 2 = 14/1/2012

Etc etc

Thanks a mill
A

[swuehl]

I think you want to have a look at the weekend() function:

WeekEnd(date [, shift = 0 [,weekoffset = 0]])

Returns a value corresponding to a timestamp with the last millisecond of the last date ( Sunday ) of the calendar week containing date The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the week which contains date. Negative values in shift indicate preceding weeks and positive values indicate succeeding weeks. If you want to work with weeks not starting midnight between Sunday and Monday, indicate an offset in days in weekoffset. This may be given as a real number indicating days and/or fractions of a day.

Examples:

weekend ( '2006-01-12' ) returns '2006-01-15' with an underlying numeric value corresponding to '2006-01-15 23:59:59.999'

weekend ( '2006-01-12', -1 ) returns '2006-01-08' with an underlying numeric value corresponding to '2006-01-08 23:59:59.999'

weekend ( '2006-01-12', 0, 1 ) returns '2006-01-16' with an underlying numeric value corresponding to '2006-01-16 23:59:59.999'

[Jason_Michaelides]

From the F1 help:

WeekEnd( date [, shift = 0 [,weekoffset = 0]] )

Returns a value corresponding to a timestamp with the last millisecond of the last date ( Sunday ) of the calendar week containing date The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the week which contains date. Negative values in shift indicate preceding weeks and positive values indicate succeeding weeks. If you want to work with weeks not starting midnight between Sunday and Monday, you may indicate an offset in days in weekoffset. This may be given as a real number indicating days and/or fractions of a day.

Example:

weekend ( '2006-01-12' ) returns '2006-01-15' with an underlying numeric value corresponding to '2006-01-15 23:59:59.999'

weekend ( '2006-01-12', -1 ) returns '2006-01-08' with an underlying numeric value corresponding to '2006-01-08 23:59:59.999'

weekend ( '2006-01-12', 0, 1 ) returns '2006-01-16' with an underlying numeric value corresponding to '2006-01-16 23:59:59.999'

Hope this helps,

Jason

[CELAMBARASAN]

Hi,

     Try with WeekEnd function

     like WeekEnd(Date)

celambarasan

[jagannalla · Accepted solution]

Try this..

Load

'Week' & Week(WeekEnd(Date([Date received]))) as WeekNo

hope it helps you.

[jagan]

Hi,

Try using WeekEnd() to get weekend date of the given date

WeekEnd([Date received]) as Week

Hope this helps you.

Regards,

Jagan.

[#7]

Guys thank you all for the responses , i simply used weekend(Date) as weekending

Worked a treat, but glad to learn all of the above date functions