Custom Fiscal Year

[lessn]

My customer has a fiscal year that begins on December 1 of the previous year and ends on November of the current year. They also have fiscal weeks that always begin on Thursday and must be atleast four days. Every week can only consist of days in that month and so the if the final week ends on a Thursday it rolls up the remaining monthly days. So far the only way I can accomplish this is with hardcoded dates which is too much work to maintain. I put a snippet of the code for just a few months to illustrate.

Switch (
//Dec 2004
cdate({@Dtime1})>=datevalue("Dec. 1 2004") and cdate({@Dtime1})<=datevalue("Dec. 9 2004"),"2004 12/01-09",
cdate({@Dtime1})>=datevalue("Dec. 10 2004") and cdate({@Dtime1})<=datevalue("Dec. 16 2004"),"2004 12/10-16",
cdate({@Dtime1})>=datevalue("Dec. 17 2004") and cdate({@Dtime1})<=datevalue("Dec. 23 2004"),"2004 12/17-23",
cdate({@Dtime1})>=datevalue("Dec. 24 2004") and cdate({@Dtime1})<=datevalue("Dec. 31 2004"),"2004 12/24-31",
//Jan 2005
cdate({@Dtime1})>=datevalue("Jan. 1 2005") and cdate({@Dtime1})<=datevalue("Jan. 6 2005"),"2005 01/01-06",
cdate({@Dtime1})>=datevalue("Jan. 7 2005") and cdate({@Dtime1})<=datevalue("Jan. 13 2005"),"2005 01/07-13",
cdate({@Dtime1})>=datevalue("Jan. 14 2005") and cdate({@Dtime1})<=datevalue("Jan. 20 2005"),"2005 01/14-20",
cdate({@Dtime1})>=datevalue("Jan. 21 2005") and cdate({@Dtime1})<=datevalue("Jan. 27 2005"),"2005 01/21-27",
cdate({@Dtime1})>=datevalue("Jan. 28 2005") and cdate({@Dtime1})<=datevalue("Jan. 31 2005"),"2005 01/28-31",
True,"Not defined")

Assistance appreciated..

 

[Madawc]

You can simplify a lot by putting cdate({@Dtime1}) in a formula field, which you could then access. And tests can be
@YourDate in [Datetime(2005, 1, 14) to Datetime(2005, 1, 20)]

Beyond that, look at DateAdd and DatePart. Ways of adding entier weeks, months or years.

It helps to give your Crystal version, since newer versions have extra options, and some extra problems. My workplace is currently upgrading from Crystal 8.5 to Crystal 10, both using databases created by SQL 8.

Madawc Williams (East Anglia, UK)

 

[lessn]

Thanks for the response. I am running CR 9. The datetime field shown is a formula (@) and the formula I posted was intended to pull records in the fiscal week range. It also formats the week regardless if there are no hits on the begin or end date for that range. What I am trying to figure out is a way to not have to hard code each date range as it changes each year, month. Your code is cleaner and gives me this now:
Switch (
//Dec 2004
{@Dtime1} in [Datetime(2004, 12, 1) to Datetime(2005, 12, 09)],"Dec 01-09",
{@Dtime1} in [Datetime(2004, 12, 10) to Datetime(2005, 12, 16)],"Dec 10-16",
{@Dtime1} in [Datetime(2004, 12, 17) to Datetime(2005, 12, 23)],"Dec 17-23",
{@Dtime1} in [Datetime(2004, 12, 24) to Datetime(2005, 12, 09)],"Dec 24-31",
//Jan 2005
{@Dtime1} in [Datetime(2005, 1, 1) to Datetime(2005, 1, 06)],"Jan 01-06",
{@Dtime1} in [Datetime(2005, 1, 07) to Datetime(2005, 1, 13)],"Jan 07-13",
{@Dtime1} in [Datetime(2005, 14, 1) to Datetime(2005, 1, 20)],"Jan 14-20",
{@Dtime1} in [Datetime(2005, 1, 21) to Datetime(2005, 1, 27)],"Jan 21-27",
{@Dtime1} in [Datetime(2005, 1, 28) to Datetime(2005, 1, 31)],"Jan 28-31",
True,"Not defined")

which produces the same result.

I appreciate your help