"Week of" calculation

[pspafford]

Hey kids.

I need a calculation that will look at a date and return the Monday of that week. e.g. If date = 8/18/2001, WeekOf_c = 8/15/2001

I know that I can do this with a long Case statement, testing for which day of the week it is, then subtracting the appropriate number:

Case(DayName(date) = Tuesday, date - 1,

DayName(date) = Wednesday, date - 2,

... and so on

but is there any other way. I like to keep my calculations as short as possible.

Thanks, eh.

Paul

[rdhaden]

Case(DayofWeek(Date) = 1, Date + 1, Date - (DayofWeek(Date) - 2))

[Ocean West]

Bravo RD, very simple...

[rdhaden]

My second post, and I got a smiley face already. Thanks!

[pspafford]

Oh my God, rd. I bow in the presence of your genius.

I tried it out, and it works, of course, but for the life of me, I can't figure out why it works.

Please enlighten a simple creature.

... and thanks.

Paul

[rdhaden]

Case(DayName(date) = Tuesday, date - 1,

DayName(date) = Wednesday, date - 2,

... and so on

The answer why is in your "and so on" which indicates you recognize there is a pattern (i.e., date -1, date-2, date-3, etc.) The number you subtract from date can be simplified to DayofWeek - 2, since Monday is the second day of the week (If Monday(2), subtract 2-2, or none. If Tuesday(3) subtract 3-2, or one). Then you just need a special case for Sunday. If the week began with Monday (i.e., DayofWeek(Monday)=1 instead of 2), you could do it without the Case.

[pspafford]

Got it. Pretty smart, rd.

Thanks.

Paul