# Thread: Measuring Three Date (2000 SR-2)

1. ## Measuring Three Date (2000 SR-2)

I am using an expression in a query to measure the number of days from one date to another. The fields are related to a corrective action style report where a DueDate is established, and then the actual CloseDate is captured at the appropriate time. Using this expressions I am able to calculate the number of "overdue" days for those items closed late.

Overdue: [CloseDate]-[DueDate]

It works great. However, there is an allowance for an ExtensionDate to be granted. That date is always later then the original DueDate and is entered in it's own separate field [ExtDate]. The problem I am running into is how to subtract the CloseDate from either the DueDate or ExtDate, provided the ExtDate was granted. In many cases an extension is not requested so there are many time that field is null. How do I get the expression to recognized the latest date between the DueDate and ExtDate, and to take into account that the ExtDate may be null (the DueDate is always required).

2. ## Re: Measuring Three Date (2000 SR-2)

Try
Overdue: [CloseDate]-nz([ExtDate],[DueDate])

3. ## Re: Measuring Three Date (2000 SR-2)

Great. Thanks Doug.

4. ## Re: Measuring Three Date (2000 SR-2)

Doug, one other issue has come up. In many cases if the CloseDate is prior to the actual DueDate or ExtDate it results in a (-) number. When I total all of the number up to calculate how many of the corrective actions were overdue, and by how many days total, and how many days average, the (-) number sqews the final numbers. How can I translate the (-) number into a "0" so it doesn't affect the calculated numbers.

5. ## Re: Measuring Three Date (2000 SR-2)

In one column:

Overdue: IIf([CloseDate]<Nz([ExtDate],[DueDate]),0, [CloseDate]-Nz([ExtDate],[DueDate]))

Or in two columns:

Diff: [CloseDate]-Nz([ExtDate],[DueDate])
Overdue: IIf([Diff]<0,0,[Diff])

6. ## Re: Measuring Three Date (2000 SR-2)

Thanks. That works great.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•