Beginning of week

[strom99]

Hi

I have a field (BILL_RUN_DATE) which I need to group my report by. There are no weekend dates in this field, but I need to make sure that when grouped, the group header would say 03/19 or 03/26 to show that Monday was the beginning of the week. I tried the grouping option 'for each week' but the group headers came out with wrong dates. How do I define Monday as the first day of the week?

SCR version 8

Thanks

 

[ChrisSharratt]

I've come across the problem before. The way I solved it was to use the "DayOfWeek" option... This returns a number for the day of the week (1 for Sunday, 2 for Monday, etc.)...

Now subtract the numbers of days away from your date (If Monday subtract 0, Tuesday, subtract 1, Weds subtract 2, etc.)

Combine this with an "if" statement, as follows:
Formula @StartOfWeek:

// Check to that the date is not a Sunday
If (DayOfWeek({Bill_Run_Date}) >= 2
// The Monday is BillRunDate - DayOfWeek - 2
then ({Bill_Run_Date} - (DayOfWeek({Bill_Run_Date}) - 2))
// else must be a Sunday - so add one day...
else ({Bill_Run_Date} + 1)

Okay, I should have used variables, but couldn't be bothered ;-)

Group the report by the new formula "@StartOfWeek".

Hope that helps, Chris

 

[kenhamady]

A simpler result would be to group on the original field each week, and delete the group name field (which will be a Sunday). In it's place on the group header put the following formula field:

{Bill_Run_Date} - DayOfWeek({Bill_Run_Date}) +2

This will print the Monday of each week. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[ppark001]

Just wanted to let you know that the formula for week beginning is great and just what I needed.

Thanks,

Paula Park