Calculate number of days between 2 dates
Hi - I can't work out what I'm doing wrong, and some help would be much appreciated.
I'm trying to get a field to calculate the number of days a complaint file is open. If the file has been closed, it should calculate the time from when the complaint was received until the date the file was closed. If the file is open, it should calculate the time from when the complaint was received until today's date. The formula I'm using is:
If ( IsEmpty(DateFileClosed) ; DateFileClosed - ComplaintReceived ; Get(CurrentDate) - ComplaintReceived )
However, I can't get this to generate meaningful results.
Let's say I put 1/02/2010 (DD/MM/YYYY format) in ComplaintReceived, and leave DateFileClosed empty, this gives me "-733804" if I have the result as a number, or "?" if I have the result as a date. If I then put 4/02/2010 in DateFileClosed, this gives me "733810" if I have the result as a number, or "7/02/2010" if I have the result as a date.
I'm a newbie and am using Pro 10 on OSX.