Using a Formula to determine time difference between date fields - but it's behaving unexpected if one of the date fields is blank:

Using a Formula to determine time difference between date fields - but it's behaving unexpected if one of the date fields is blank:

So in our "Clients" module, in each client's page, we have a field that reflects the date they were most recently visited. 

We use this to calculate the number of days since their last visit, and then categorize them into "< 30 days ago since visit", "31-60 days ago since visit", etc, and then deliver this info to our sales reps' home pages through the Analytics module. 

  2. If(
  3. Tonumber(${Clients.Date of Most Recent Visit})==0,
  4. 2000,
  5. If(Tonumber(${Clients.Mass update})==0,Datecomp(Now(),${Clients.Date of Most Recent Visit})/1440,
  6. Datecomp(Now(),${Clients.Date of Most Recent Visit})/1440)
  7. )

However, when the "Date of Most Recent Visit" field is blank, this function always returns us 0. Formula return type for this is set to 0. 


Info
Moderation Update (April 2026):
The recent enhancement to formula fields addresses how null values are handled. Please read the announcement below to know about it.
Announcing a change to how null values in formulas are computed in Zoho CRM