Date Formula

[crdba]

I am having a problem creating a formula in CR11. I have two fields. One called Create_time that gives me a date and time of a ticket created and Date_Closed which shows the Date and time a ticket was closed. When I run my report that prompts for a range of dates, I will get some null values for the Date closed field. Is there a way to create a formula that will automatically populate the Date Closed field to always show the last date of the month when I run the report for each new month.

 

[synapsevampire]

Instead of the field create a formula:

if isnull({table.Date_Closed}) then
dateserial(year(currentdate),month(currentdate)+1,1)-1
else
{table.Date_Closed}

-k

 

[crdba]

THanks, I will try it and it works, but only got one problem. The report I am running is for the month of August and it fills in the Null dates for the month of September. How do I modify it to show August and if I run the report for September, the end date for September??

 

[synapsevampire]

I've no idea what you're asking, sorry.

Perhaps you should try the solution before explaining your problems.

-k

 

[crdba]

What I am asking is with the formula you gave me, how do I get it to show 8/31/07 for the null values for the Date_Closed field and if I run this same report for September to show 9/30/07. I hope that clears it up.

 

[synapsevampire]

Yes, try the formula, that's what it automatically does.

-k

 

[crdba]

For some reason when I run the report for the month of August because I have it set up where my report prompts me for dates(1-AUG-07 - 31-Aug-07). For my null values it will give me a date of 9/30/07. I thought it would give me the date of 8/31/07??

 

[synapsevampire]

WOuld be Lovely were you to state where and what you're referring to.

And post whatever formula you've used.

When you say for your null values, do you mean for your null values in the prompt, or in the report.

I'm flailing here to understand your brief descriptions of what you have and what you need.

Try posting examples of what you have and what you need.

-k

 

[crdba]

if isnull({table.Date_Closed}) then
dateserial(year(currentdate),month(currentdate)+1,1)-1
else
{table.Date_Closed}


This is the formula I am using. I have parameter that prompts for date ranges. In my prompt I use 1-Aug-07 to 31-Aug-07. When the report comes back the Date_Closed shows the value of 9/30/07 when I thought it should show 8/31/07.

 

[synapsevampire]

<laffin>

OK, got it, no wonder there's a disconnect here.

You asked for "Is there a way to create a formula that will automatically populate the Date Closed field to always show the last date of the month when I run the report for each new month."

The last date of the month that you run the report, not the last day of the month you entered parameters for.

Try:

if isnull({table.Date_Closed}) then
dateserial(year({?MyDateClosed}),month(?MyDateClosed)+1,1)-1
else
{table.Date_Closed}

 

[crdba]

dateserial(year({?MyDateClosed}),

On this part of the formula, it tells me I need a date??

 

[dgillz]

Dateserial(Year(whatever value)) does not translate to a date. You need a month and day as well. Hence the error.

Please try the formula SV suggested.


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

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[crdba]

dgillz,

I tried the formula SV suggested... still getting the same error.

dateserial(year({?MyDateClosed})<------ the MyDateClosed is highlighted Blue and tells me I need a date there when I save the formula.

 

[lbass]

Go to the field explorer->parameters->edit and check the datatype of your parameter. It needs to be a date.

-LB

 

[crdba]

lbass,

It isnt working. Could you please show me how the formula is supposed to look.

 

[lbass]

Exactly as SV laid it out:

if isnull({table.Date_Closed}) then
dateserial(year({?MyDateClosed}),month(?MyDateClosed)+1,1)-1
else
{table.Date_Closed}

Of course you need to substitute your actual date field for {table.Date_Closed}. Did you check the datatype of the parameter?

-LB

 

[crdba]

yes I checked the datatype. It is DateTime datatype

 

[crdba]

if isnull({table.Date_Closed}) then
dateserial(year({?MyDateClosed}),month(?MyDateClosed)+1,1)-1
else
{table.Date_Closed}

Can you put some type of variables in the formula for me so I can get a better idea. And what do you mean by this:

Of course you need to substitute your actual date field for {table.Date_Closed}. Did you check the datatype of the parameter

 

[lbass]

Please post your actual formula.

-LB

 

[crdba]

actual formula:

if isnull({Table.Date_Closed})then
dateserial(year({?DateRange}), month(?DateRange)+1,1)-1
else
{table.Date_Closed}


This is what I have in my editor.