2 Replies Latest reply on Feb 7, 2010 5:58 AM by caz3000

# Calculate number of days between 2 dates

### Title

Calculate number of days between 2 dates

### Post

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.

• ###### 1. Re: Calculate number of days between 2 dates

It needs to be the other way round:

If ( IsEmpty ( DateFileClosed ) ; Get (CurrentDate) - ComplaintReceived ; DateFileClosed - ComplaintReceived )

or shortly:

If ( not DateFileClosed ; Get (CurrentDate) ; DateFileClosed ) - ComplaintReceived

The result type should be Number, and the calculation must be unstored.

• ###### 2. Re: Calculate number of days between 2 dates

Thank you so much - I used your short version and it worked perfectly!

(I clicked the Kudos thing and then I clicked ok before realising this gave a '1' result, so I'm not sure if there was a way to give you more than '1' but I can't seem to change it now - sorry!)