calculate datedif and stop once column filled in

Question

I want to calculate days on wait list (0 if the assessment date column is not filled in), calculate date from today minus the referral received date, and then stop the calculation when the date of assessment is added.

I have this formula which nicely counts the days on wait list, but I want it to stop when the client has been assessed...how do I do this?

Formula:

=IF(ISBLANK(G7),0,DATEDIF(G7,TODAY(),"d"))

Referral received is G4, assessment date is L4

Answer 1

Saying your formula is in H7, G7 receiving Date, L7 assessment date, Enter Excel Options, Formulas, check Enable Iterative calculation, Maximum iteration 1 and write the following formula in H7:
=IF(L7="",IF(ISBLANK(G7),0,DATEDIF(G7,TODAY(),"d")),H7)
It will give you circular reference if you don't change the options of Enable Iterative Calculation
And it will freeze the result when assessment date L7 is not empty
if L7 is empty it will calculate the number of days the way you need

Answer 2

You should be able to simply wrap it in another IF statement...

=IF(ISBLANK(L4), IF(ISBLANK(G7),0,DATEDIF(G7,TODAY(),"d")), "")

This checks the condition of L4 first. If the assessment date has been entered, the formula returns nothing (blank). If L4 is blank, then it runs your formula as you have it.

Answer 3

=IF(ISBLANK(G4),"",IF(ISBLANK(L4),DATEDIF(G4,TODAY(),"d"),DATEDIF(G4,L4,"d")))