Date Formula

[Steve95]

Hi All

I need a date formula to represent a daterange as: 01/01/2005 31/12/2005

basically on the 10th of every month I send out automated reports via the cmc, which show last months stats. But on the 10th of Jan 06 I wanted the formula to represent the past year. For example on the 10th Jan it should have shown 01/01/2005 31/12/2005.

The reports are year based basically showing a rolling figure for that year so the formula must represent as follow

01/01/2005 31/01/2005
01/01/2005 28/02/2005
01/01/2005 31/03/2005
01/01/2005 so forth

And in Jan 06 it should the whole year.

In 10 feb 2006

01/01/06 31/01/06
etc, etc

Any ideas?

Many Thanks in advance

 

[Madawc]

If you want a whole calendar year, the easiest way is to extract the year from the date. Selecting using Year({your.date}) = Year(currentdate) should do this, though it depends on your version of Crystal.

For January, you want something different. Test If Month(currentdate) = 01. Or you can use If DatePart("m", currentdate) = 01, which does the same thing.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Steve95]

Hi Madawc

thanks for the input, the first formula suggested will not resolved the issue in jan of each year as this brings back a boolean value.

The 2nd formula again brings back a boolean value, I need a date field outputted.

Many Thanks

 

[Madawc]

You need to define a start date and an end date. Do as follows:
Define the month before the current date, @Priormonth:

 DateAdd("m", -1, currentdate)

@Startdate can be:

 Date((Year(@Priormonth), 01, 01)

Note that the format is (YY MM DD)

For the end date, you could use logic to work out the last day of the month. Allowing for leap-years.

Then make a selection

{your.date} >= @Startdate 
and
{your.date} =< @Enddate

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Steve95]

Hi their...

thanks for getting back to me....

your last formula statement "make a selection", are you referring to create 2 parameters?

The reason Iam asking is cause these reports are automated and inputted to customer email inbox.

Many Thanks......

 

[lbass]

Try:

if month(currentdate) = 1 then
date(year(currentdate)-1, 1,1) to date(year(currentdate(-1,12,31) else
date(year(currentdate),1,1) to currentdate-day(currentdate)

-LB

 

[Steve95]

Hi Ibass

I tryed the above formula, but I getting a error message
"The result of a formula cannot be a range", any ideas?

Many Thanks

 

[lbass]

Sorry. That should be:

if month(currentdate) = 1 then
{table.date} in date(year(currentdate)-1, 1,1) to date(year(currentdate(-1,12,31) else
{table.date} in date(year(currentdate),1,1) to currentdate-day(currentdate)

-LB

 

[Steve95]

Hi lbass

thanks for the above but that has given me back boolean, I need a date field to output.

Many Thanks

 

[lbass]

What exactly are you trying to do? What I gave you is a record selection formula, to be placed in report->selection formula->record.

-LB

 

[Steve95]

I need a formula or 2 formulas fields which represent a start date as 01/01/2005 and then either in the same formula or another formula field representing end of the last month ie. 31/01/2005. Both formula's must be data type date as above. The first formula must remain the same 01/01/2005 throught out the year as this report is yearly based and the 2nd formula reflecting end of the last month. So in March 05 the date should reflect 01/01/2005 thru to 28/02/2005. The reason for reflecting the previous month is cause these reports are automated and outputted via the cmc to the users reflecting the previous month's data. Also come Jan 06 I need the formula to reflect as follows 01/01/2005 thru to 31/12/2005 and then in Feb 01/01/2006 thru to 31/01/2006.

I hope this helps......

Many Thanks

 

[lbass]

You still have not explained how you will use these formulas. To get a beginning date and an end date, use:

//{@start}:
currentdate - day(currentdate)-
day(currentdate - day(currentdate))+1

//{@end}:
currentdate - day(currentdate)

-LB

 

[dgillz]

The record selection logic should be:

{YourDate} in LastFullMonth

and to display the date range:

Totext(minimum(LastFullMonth))&" to "&totext(maximum(LastFullMonth))

format your date within the last formula as needed.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[Steve95]

Thanks lbass, these formula will be used stand alone to represent the data present in the report (in the report header). Their is currently a boolean in place similar to the one you provided on the record selection which keeps the years rolling for the data.

I think the real test for this formula will be come Jan 2007, hopefully the formula;s will reflect 01/01/2006 thru to 31/12/2006.

Many Thanks for your help.

 

[lbass]

Actually, I realize my last post showed the last full month. The start formula should have been:

//{@start}:
dateserial(year(currentdate-day(currentdate)),1,1)

-LB

 

[Steve95]

Thanks lbass...