Date Formula

[TEM3]

I need a formula to calculate turn-around-time of assignments. If the assignment is completed, the following formula works. However, if there is no completion date, I get a zero when what I want is the number of days between the assignment date and the current date.

if IsNull ({ALL_ASSIGNMENTS.Date Completed}) then (currentdate - {ALL_ASSIGNMENTS.Date Assigned}) else
({ALL_ASSIGNMENTS.Date Completed} - {ALL_ASSIGNMENTS.Date Assigned})

 

[dgillz]

If the item is not completed, is the date field truly null, or is it a zero? To test this, write a quick formula:

IsNull ({ALL_ASSIGNMENTS.Date Completed})

Place it on the report and see if it shows up as true or false on items that are not completed.

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

http://www.gainfocus.biz

 

[TEM3]

I inserted your formula. Comes up "False" Date Completed or no Date completed. No "True"'s.

 

[TEM3]

Not too pretty, but this formula works:

if totext({ALL_ASSIGNMENTS.Date Completed}) = "" then (currentdate - {ALL_ASSIGNMENTS.Date Assigned}) else
({ALL_ASSIGNMENTS.Date Completed} - {ALL_ASSIGNMENTS.Date Assigned})