How to determine weekending date for a given date? 2

[rdsigrist]

I am quite surprised that Crystal does not have a simple function called WeekEndDate({OrderDate.date}) that simply returns the Saturday date of any given week.

Sorting transactions and summarizing them by weekendingdate solutions tend to require the use of grouping or printing commands. If you want to see the weekending date in a detailed report, these do not work. Here is what I want to see:

Order No Order Date Week Ending
123456 12/06/2010 12/11/2010
567899 12/14/2010 12/18/2010

 

[lbass]

{table.date}-dayofweek({table.date})+7

-LB

 

[MCuthill]

LB,

Curiosity has now gotten the better of me. Just to make sure I understand this method, as I think I finally had an "ah-ha" moment on your formula.

Formula: {table.date}-dayofweek({table.date})+7

You are subtracting number of days (the numeric value of the date (Sunday being 1, and Saturday being 7)) from the date itself, always leaving the value of the Saturday ending the prior week. By then adding 7 (days) back to this value, you will always be left with the Saturday ending the same week as the date field?

Using today's date as an example:
{Table.Date} = Dec 23, 2010 (Thursday)

= {table.date} - DayOfWeek({table.date}) + 7
= 12/23/2010 - DayOfWeek(12/23/2010) + 7
= 12/23/2010 - 5 + 7
= 12/18/2010 + 7
= 12/25/2010

Not sure why I didn't follow this one the first few times I read it, awesome way to find the end of a week! I have only used the DayOfWeek function on rare occasion, and often forget about it's potential. I assume this could easily be adjusted should someone need a Friday as the week ending day?

Thanks LB, Happy Holidays to you and yours!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

For a week ending Friday:

{table.date}-dayofweek({table.date},crSaturday)+7

-LB