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.

 

[lbass]

What is the date field you are working with? You should be substituting that for {table.Date_Closed}. Drag it from your field list into the formula.

-LB

 

[crdba]

I am using Date_Closedate Time field

 

[crdba]

I am sorry... you might laugh at this, but I am just not understanding what your asking me to do... maybe its because I am a little frustrated with this....LOL.. I am not a big fan of programming.

 

[lbass]

Date_Closed" is not what the field looks like if you drop it into a formula--it is missing the table name and curly brackets.

Create the formula in the field explorer->new and instead of typing in the field name, select it from the field list and add it to the formula by double clicking on it so that it is put into the formula where you have your cursor placed.

-LB

 

[crdba]

this is what it looks like: HPD_HD.Date_Closed when I drop it into my edtitor.

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

This is what my actual text looks like in my editor.

 

[crdba]

I used the cursor and dropped it in the formula, still getting errors.

 

[lbass]

Sorry, I just realized that what you are showing is a date range parameter.

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

-LB

 

[crdba]

It worked!!! Thank you very much. I was starting to pull my hair out over here.