7 Replies Latest reply on Oct 16, 2013 7:20 PM by hferrell

# Calculation of days between dates.

### Title

Calculation of days between dates.

### Post

Simple enough--except when one of the dates is missing.  If one date is missing, the result is an extraneous number like +/- 733810.

If date/dates are not complete, how can I get the results of the calculation to be “Unknown” rather than getting an extraneous number?

Having the actual date is not possible in some cases.  Sample calculations: 2009-2006= 0; No Date-1/31/2009= 733437; 5/17/2010-1997= 733909; 9/1/2009-No Date= -733651; No Date-No Date= “Blank“.

Thanks,

Hal

• ###### 1. Re: Calculation of days between dates.

Case(
date1*date2 ; Abs ( date2 - date1 )
)

• ###### 2. Re: Calculation of days between dates.

If ( Not IsEmpty ( Date1 ) and Not IsEmpty ( date2 ) ; Date2 - Date1 )

Note:

When date fields are empty, they often evaluate as a value of zero and you get the number of days from 12/31/000 to the date or the negative of that number of days.

• ###### 3. Re: Calculation of days between dates.

Thanks..That is what I have; however, I would like for the calculation of the zero value to post as "unknown" and not the negative number.  Can that be done?

Thanks again,

Hal

• ###### 4. Re: Calculation of days between dates.

Raybaudi's example will not return a negative value.

You can try specifying "unknown" as the "false" result in Raybaudi's expression.

Case(
date1*date2 ; Abs ( date2 - date1 )
"Unknown")

But I prefer not to return text in a number calculation. There are other ways that you can use to display the word "unknown" on a layout.

• ###### 5. Re: Calculation of days between dates.

OK, what are some other ways to display "unknown"?

Or could I simply add two lines like ... If value is > 700,000, then " " and If value is < -700,000, then " ".  If so, how would you write those statements below and beyond the values?

Hal

• ###### 6. Re: Calculation of days between dates.

It's not the case statement that I take issue with, it's the data type. A number result and a text result in the same field can, in some cases, cause issues--especially if you need to sort or search on this field.

A separate calculation field can use IsEmpty() to return "unknown" if the above calculation returns null due to an empty date field.

A conditional format can cause a Layout Text object consisting of the word "unknown" to be visible when the calculation field is empty...

• ###### 7. Re: Calculation of days between dates.

Understood.  Thanks for the help.

Hal