How to get a weekly or 7 days date range in Crystal Report

Question

How can I get 7 days or weekly, depending on the date range in the Crystal Report formula fields?

Example: Date Range from March 01, 2014 to March 31, 2014 Output: Week 1 (March 01 to March 07) Week 2 (March 08 to March 14) Week 3 (March 15 to March 21) Week 4 (March 22 to March 28) Week 5 (March 29 to March 31) 

Answer 1

RangeWeekSplitter custom function runs for one year. Crystal syntax.

 Function (dateTimeVar dFrom, dateTimeVar dTo) // First day of the year local dateTimeVar dBegCurrYear:= Date (Year(dFrom), 1, 1); // Day of year (1 to 365 or 366 in a leap year) local numberVar nFrom:= DatePart ("y", dFrom); local numberVar nTo:= DatePart ("y", dTo); local numberVar i; local numberVar iTo; local numberVar nDaysInWeek:= 7; // number of days in the week local numberVar nWeek:= 0; // counter weeks local stringVar sResult:= ""; // output string for i:= nFrom to nTo step nDaysInWeek do ( nWeek:= nWeek+1; iTo:= i+(nDaysInWeek-1); if(i+nDaysInWeek > nTo) then iTo:= nTo; // generate output string sResult:= sResult + chr(13)+ "Week " + CStr(nWeek) + " (" + CStr(DateAdd ("y", i-1, dBegCurrYear), "MMMM d") + " to " + CStr(DateAdd ("y", iTo-1, dBegCurrYear), "MMMM d") + ")"; ); sResult; 

Usage example:

 // Date range local dateTimeVar dFrom:= Date (2016, 1, 14); local dateTimeVar dTo:= Date (2016, 3, 4); RangeWeekSplitter (dFrom, dTo); 

Answer 2

You can use DatePart with "ww" . See IBM Knowledge Center :

DatePart (intervalType, inputDateTime)

...

ww: week of the year (from 1 to 53 with firstDayOfWeek and firstWeekOfYear, which determines the exact days of the first calendar week of the year)

In combination with DatePart ("w", inputDateTime) or DayOfWeek(inputDateTime) receiving the day of the week, you can calculate your first and last day of the current calendar week.

So, for one specific date ( inputDateTime ) this will be your "RangeWeek" formula:

 Function (DateTimeVar inputDateTime) NumberVar cw := DatePart("ww", inputDateTime); DateTimeVar first := DateAdd("d", 1 - DayOfWeek(inputDateTime, crMonday), inputDateTime); DateTimeVar last := DateAdd("d", 7 - DayOfWeek(inputDateTime, crMonday), inputDateTime); "Week " + ToText(cw) + " (" + ToText(first) + " to " + ToText(last) + ")" 

You need to provide ToText with the format string you need.

Example:

 Input: "August 23, 2017" Output: "Week 34 (August 21 to August 27)" 

This simplifies the formula in which you get the date range.

 DateTimeVar from := ...; DateTimeVar to := ...; NumberVar cw; NumberVar count := 0; StringVar output := ""; for cw := DatePart("ww", from) to DatePart("ww", to) do ( output := output + chr(13) + RangeWeek(DateAdd("d", 7*count, from)); count := count + 1; ); output