Finding a date for the start date of a week number ("ww")

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kristibaer - 05 Nov 2007 21:17 GMT

Can you please advise what the formula would be to convert the week number
into the date of the first day of that week? For example:

This is week 45. The first day was Sunday, and first date of week 45 is
Nov-4-2007.

I am writing a qury to produce daily, weekly and monthly sales totals. I
know part of this can be filtered in Report Writer, but would like to see the
data displayed in the query as well.

Thanks!

Reply to this Message

KARL DEWEY - 05 Nov 2007 22:25 GMT

This formula is close --
Expr2: DateAdd("ww",44,DateSerial(Year(Date()),1,1))

Signature

KARL DEWEY
Build a little - Test a little

> Can you please advise what the formula would be to convert the week number
> into the date of the first day of that week? For example:
[quoted text clipped - 7 lines]
>
> Thanks!

Reply to this Message

Kristibaer - 05 Nov 2007 22:55 GMT

Thanks for the help. The formula below only brings up the first date of the
week indicated in the formula (i.e.44). I tried playing with that, but it
only displays the date for the week number given regardless of the actual
week and won't run without it. Here's the results for Week 1-2 when I sub'd
44 for 53. Week number is already generated by using the "ww" function:

Week #    ordrdate    Daily Sum    Week Of - SHOULD BE
1    1/2/2007    $6,367.80    1/7/2008 12/31/06
1    1/3/2007    $12,394.70    1/7/2008 12/31/06
1    1/4/2007    $98,513.04    1/7/2008 12/31/06
1    1/5/2007    $82,614.36    1/7/2008 12/31/06
2    1/8/2007    $4,018.56    1/7/2008 01/07/07
2    1/9/2007    $18,361.48    1/7/2008 01/07/07
2    1/10/2007    $27,291.39    1/7/2008 01/07/07
2    1/11/2007    $18,457.89    1/7/2008 01/07/07
2    1/12/2007    $33,176.11    1/7/2008 01/07/07

Did I miss something?

Thanks,

> This formula is close --
> Expr2: DateAdd("ww",44,DateSerial(Year(Date()),1,1))
[quoted text clipped - 10 lines]
> >
> > Thanks!

Reply to this Message

KARL DEWEY - 05 Nov 2007 23:44 GMT

I assumed it was only this year and you did not have a datetime field but
just weeks.
Now to me it seems like you want the Sunday of the week of your order-date.
If that is true then try this --
DateAdd("d",-Format([OrderDate],"w")+1,[OrderDate])

Signature

KARL DEWEY
Build a little - Test a little

> Thanks for the help. The formula below only brings up the first date of the
> week indicated in the formula (i.e.44). I tried playing with that, but it
[quoted text clipped - 31 lines]
> > >
> > > Thanks!

Reply to this Message

Kristibaer - 06 Nov 2007 16:51 GMT

Once I replaced [OrderDate] with the database field name, worked like a charm!

Thanks,
Kristi

> I assumed it was only this year and you did not have a datetime field but
> just weeks.
[quoted text clipped - 37 lines]
> > > >
> > > > Thanks!

Reply to this Message