Date Diff with 4 Date fields

[talkiechick]

I need to calculate a way to count the number of days a deal takes to close. The problem being we only want to count days that it is actually being worked on. So I have four date fields: Open Date, Inactive Date, Reactive Date, and Close Date.

I wanted to create an expression that counted the days of Open date to close date, minus the days between inactive and reactive date. I thought that would be the easiest way to get the number i wanted. The problem is the Inactive and reactive dates may be null, only a small handful of deals with have inactive and reactive dates, but i want it to account for those as well.

Is there a simple was to do this. I originally created two date diff expressions, and then subtracted those expressions from each other, that worked except i have enter parameter values occurring.

 

[PNGBill]

Try two calculations

One is the Start Date and End date and the other is the Inactive and Reactive Dates.

Assumption here is that while there must be a Start Date for any occurrence of End date there also must be a Inactive Date for any occurrence of Reactive date.

Calculation One is a given and returns an integer.

Calculation two is only done if Reactive Date is Not Null and if so then this will work.

Final result is calculation One, if Reactive date is null or calculation one less calculation two as the fall back of "If then"

This way, any failure of calculation two won't be an issue as you wont be using it unless there is a value in Reactive date.

 

[talkiechick]

That is the issue, there may not always be a Inactive date. Some deals go away completely and if they have been inactive long enough it gets closed out. But some deals will not have any inactive dates. So my problem is that two dates fields (Inactive and reactive) could be null often.

 

[PNGBill]

That is the issue, there may not always be a Inactive date. Some deals go away completely and if they have been inactive long enough it gets closed out. But some deals will not have any inactive dates. So my problem is that two dates fields (Inactive and reactive) could be null often.

Yes, so your final calculation is an If Then statement that looks to Reactive date being null to decide if you just use Date diff start date , end date or the full date 4 date calculation.

Actually, only one calculation is done but when wrapped in the If Then Statement there are two options for this calculation. the two field option or the four field option.

If the the work is done on a form then the names are Controls, not fields but same If Then should apply.

 

[talkiechick]

Below are the two calculations i did, then I created the IIF statement below. It works fine, but the query is constantly asking for Total Days and Inactive Days Parameter.

Total Days: DateDiff("d",[Date Opened],[Closed Date])

Inactive Days: DateDiff("d",(IIf([Inactive Date] Is Null,Null,[Inactive Date])),(IIf([Reactive Date] Is Null,Null,[Reactive Date])))

# of Days: IIf([Inactive Days] Is Null,[Total Days],([Total Days]-[Inactive Days]))

 

[PNGBill]

Below are the two calculations i did, then I created the IIF statement below. It works fine, but the query is constantly asking for Total Days and Inactive Days Parameter.

Total Days: DateDiff("d",[Date Opened],[Closed Date])

Inactive Days: DateDiff("d",(IIf([Inactive Date] Is Null,Null,[Inactive Date])),(IIf([Reactive Date] Is Null,Null,[Reactive Date])))

# of Days: IIf([Inactive Days] Is Null,[Total Days],([Total Days]-[Inactive Days]))

IIf([Reactive Date] Is Null, DateDiff("d",[Date Opened],[Closed Date]),DateDiff(use the full four field expression))

So here you have one calculation only that either uses two fields or four fields depending on [Reactive Date] being Null or Not.

 

[talkiechick]

This what I am using, but for some reason it isn't working. It says something about an aggregate function.

# of Days: IIf([Reactive Date] Is Null,(DateDiff("d",[Date Opened],[Date Declined/Closed])),(DateDiff("d",[Inactive Date],[Reactive Date])))

 

[PNGBill]

This solution works:

SELECT tbleDateTest.StartDate, tbleDateTest.InactiveDate, tbleDateTest.ReactiveDate, tbleDateTest.EndDate, DateDiff("d",[StartDate],[EndDate]) AS OverallDays, DateDiff("d",[InactiveDate],[ReactiveDate]) AS InactiveDays, [OverallDays]-Nz([InactiveDays],0) AS NetDays
FROM tbleDateTest;

I will try my earlier idea of an iif statement but using 3 calculations works above with the third calculation, NetDays, using zero for InactiveDays where the InactiveDays calculation returns a Null.

 

[PNGBill]

This one calculation with IIf works:

SELECT tbleDateTest.StartDate, tbleDateTest.InactiveDate, tbleDateTest.ReactiveDate, tbleDateTest.EndDate, IIf(Nz([ReactiveDate],Date())=Date(),DateDiff("d",[StartDate],[EndDate]),(DateDiff("d",[StartDate],[EndDate])-DateDiff("d",[InactiveDate],[ReactiveDate]))) AS OtherTest
FROM tbleDateTest;

I am not 100% with this as there is the chance that the Reactive Date is Today and the End Date is Today, although, I guess that won't change anything.

A better test is to see the Null Entry in Reactive Date but I couldn't get this to work.

I would be happier if the test at the beginning was something like IIf(Nz([ReactiveDate],DateAdd("d",1,Date(),
This would mean that if Reactive Date was null then use today plus 1 day to test against Today.

Anyway, you now have two options that work.