Formula for Subtracting Dates

[IronRocket]

I wrote a report that posted the open date of a trouble ticket and then the close date of the ticket. I did this for the month of January. The format for both of these fields is a string for some reason and they are
'yyyy-mm-dd'.

My formula for this is
"dateValue({CallLog.ClosedDate}) - dateValue({CallLog.RecvdDate})". This worked at first then it is giving me an error message of "Bad Date Format String". I then put "date({CallLog.ClosedDate}) - date({CallLog.RecvdDate})" - still giving me an error.

Just wondering what I'm doing wrong. I'm aiming to get the count of days between the date the ticket was opened and the date the ticket was closed by the manager.

 

[AdventurGurl]

That looks correct, check and make sure it is not throwing the error because of NULLs in either of the fields.

To give an even number w/o decimal, try this:

totext(dateValue({CallLog.ClosedDate}) - dateValue({CallLog.RecvdDate}),0)

Hope that works for you

 

[IronRocket]

I tried the toText function per your advice...unfortunately, it didn't work.

The weird thing is that is was working for the first couple of refreshes (I have a parameter and was putting different values in to test it).

Thanks for the advice...just wondering if you can think of any more.

btw...I'm using 8.5 here at work...I have version 10 at home, so maybe it's something with the 8.5?

 

[lbass]

You need to test for nulls--at least for the close date. Try this:

if isnull({CallLog.ClosedDate}) or
trim({CallLog.ClosedDate}) = "" then
datediff("d",date({CallLog.RecvdDate}), currentdate) else
datediff("d",date({CallLog.RecvdDate}),date({CallLog.ClosedDate}))

-LB

 

[IronRocket]

LBass - Thanks!

It worked perfectly.

I need to get better at formulas...it's just going to take some time and practice.

Thanks again!