formula help – blank date (2002 SP3)

Topic

New Reply

Hi,

I am trying to determine if the date between when a claim was opened and when it was determined (accepted or rejected) is greater than 65 days. My problem is with claims that have not been determined yet. There is a blank where the determination date should be. Can someone suggest a formula I could use to insert todays date if the date if blank. eg open date (col c) – determined date (col d) = D6-C6 (Col e). Column E is formatted as a number. In Col F ^=COUNTIF(E6,”>65″). Currently Column E is showing #VALUE! where there is a blank, and Column F is returning 0. If the number of days between the open date and todays date is greater than 65 I would like to be able to count this item.

Thanks for any suggestions

capri

Reply | Quote

Replies

You could use

=IF(ISBLANK(D6),TODAY(),D6)-C6

COUNTIF is, as the name indicates, intended to count a number of cells. It’s overkill to use it to return a 0 or 1 depending on the value of a single cell. You could use

=IF(E6>65,1,0)

or

=0+(E6>65)

instead, that would be more efficient.

Reply | Quote