Calculate Date Difference in Y, M, D format

  • Thread starter Thread starter Palacelaw
  • Start date Start date
P

Palacelaw

I want to calculate the difference between Opened_Date and Date_Completed in
Y, M, D format in a field called Number_Of_Open_Days with the following
conditions.

If Opened_Date is null then Number_Of_Open_Days should be null. If
Date_Completed is null then Number_Of_Open_Days should be the difference in
Y, M, D format between Today and Opened_Date.

I've tried various combinations of DateDiff with no luck.

Thanks in advance for your help.
 
Palacelaw said:
I want to calculate the difference between Opened_Date and Date_Completed in
Y, M, D format in a field called Number_Of_Open_Days with the following
conditions.

If Opened_Date is null then Number_Of_Open_Days should be null. If
Date_Completed is null then Number_Of_Open_Days should be the difference in
Y, M, D format between Today and Opened_Date.
Click to expand...


Try using:
DateDiff("d", Nz(Date_Completed, Date()), Opened_Date)

I don't understand what you mean by
"the difference in Y, M, D format"
 
Marshall Barton said:
Try using:
DateDiff("d", Nz(Date_Completed, Date()), Opened_Date)

I don't understand what you mean by
"the difference in Y, M, D format"
Click to expand...

To answer your question - for example if Opened Date is 1/1/2008 and Date
Completed is 2/2/2009 then Number of Open Days would be 1 year, 1 month, 1
day.
 
Drew said:
To answer your question - for example if Opened Date is 1/1/2008 and Date
Completed is 2/2/2009 then Number of Open Days would be 1 year, 1 month, 1
day.
Click to expand...


Ahhh, then use Doug's fancy function. Make sure that you
use Nz(Date_Completed, Date()) to deal with Null completion
date.
 
Back
Top