Calculate Weekending date.

[David]

Can someone help me with this. I need to calculate the week ending
date of the first week of the year based upon a year provided by the
user. Is there a simpler way other than writing my own UDF?

[James Goodwin]

"David" <da***********@t-mobile.com> wrote in message
news:9a**************************@posting.google.c om...

Can someone help me with this. I need to calculate the week ending
date of the first week of the year based upon a year provided by the
user. Is there a simpler way other than writing my own UDF?

That depends on what week ending date and first week of the year mean.

If 1 Jan is on Wednesday do you want to come back with 4 Jan, 5 Jan, 11 Jan
or 12 Jan?

Anyway, Generally, the last day of a week is Date +7 - Day(Date) where Day()
returns the number of the day in the week (1=Sun, 2=Mon, 3=Tue,
etc...Assuming the week starts on Sunday)

Therefore DateAdd(d,-DatePart(dw,datefield),DateAdd(d,7,datefield))

However, if the datefield contains a time you may want to change the time if
you are using this in a comparison.

Regards,
Jim