Days Open (DateDiff) for Null Dates

[OldGrayMare]

Using v. 11
I have a report that shows days open for tickets based on the formula below. Some of the tickets are active, however, not having a Last Resolved Date. Those are currently showing zero (0) for days open (report line below -- see 0 after the date/time on first report line).

I want those tickets that are still open to show days open based on Current Date, not Last Resolved Date.

Can anyone offer suggestions on a formula to do that?

FORMULA:
DateDiff ("d",{HPD_Help_Desk.Reported Date} ,{HPD_Help_Desk.Last Resolved Date} )

SAMPLE REPORT LINES:
INC000000593025 Request Low Pending 8/6/2009 1:53:02PM 0 - Would like condensed version of the "Ticket Management item...
INC000000588569 Request Low Closed 8/3/2009 4:24:30PM 8/6/2009 4:32:54PM 3 - Schedule Internal Report with Details Weekly Review

Thank you,

Old Gray Mare

 

[fisheromacse]

maybe something like this:

IF isNull({HPD_Help_Desk.Last Resolved Date})
THEN
DateDiff ("d",{HPD_Help_Desk.Reported Date} ,currentdate )
ELSE
DateDiff ("d",{HPD_Help_Desk.Reported Date} ,{HPD_Help_Desk.Last Resolved Date} )


--this is off the top of my head and it has been a LONG week, so i hope it is not too horrid an idea.

 

[OldGrayMare]

Thank you, FISHEROMACSE. Logically, this should work and produce the actual days open for unresolved tickets. However, after making these changes to the report formula, I still get zero (0) for days open.

I did make one minor change to your formula as indicated below, since this is a date/time field:

IF isNull({HPD_Help_Desk.Last Resolved Date})
THEN
DateDiff ("d",{HPD_Help_Desk.Reported Date} ,currentdatetime )
ELSE
DateDiff ("d",{HPD_Help_Desk.Reported Date} ,{HPD_Help_Desk.Last Resolved Date} )

If you have any further suggestions, I would greatly appreciate them.

Thanks so much,

OGM

 

[fisheromacse]

perhaps adding if field is blank like this will help...

IF isNull({HPD_Help_Desk.Last Resolved Date})
OR {HPD_Help_Desk.Last Resolved Date}=""
THEN
.
.
.

 

[OldGrayMare]

Thank you, FISHEROMACSE. When I use the "", however (see below formula), I get an error: "A date-time is required here."

I really appreciate your work on this, and will continue to check back for updates.

OGM

 

[lbass]

Try:

IF isNull({HPD_Help_Desk.Last Resolved Date}) or
{HPD_Help_Desk.Last Resolved Date} = datetime(0,0,0,0,0,0) THEN
DateDiff ("d",{HPD_Help_Desk.Reported Date} ,currentdatetime )
ELSE
DateDiff ("d",{HPD_Help_Desk.Reported Date} ,{HPD_Help_Desk.Last Resolved Date} )

-LB

 

[OldGrayMare]

I found this suggestion on another post:

IF isNull({HPD_Help_Desk.Last Resolved Date})
THEN
DateDiff ("d",{HPD_Help_Desk.Reported Date} ,date(1900, 01, 01))
ELSE
DateDiff ("d",{HPD_Help_Desk.Reported Date} ,{HPD_Help_Desk.Last Resolved Date} )
// date(1900, 01, 01)

I tried that, but I am still getting zero for days open on those active tickets.

Any other ideas would be appreciated.

Thanks,

OGM