Help with Date Difference Expression

[Rene]

Good morning,

Based on the following:

Lost Days = DateDiff("d",Nz([MyLostDaysBeginDate]),Nz([MyLostDaysEndDate]))

Restricted Days =
DateDiff("d",Nz([MyRestrictedDaysBeginDate]),Nz([MyRestrictedDaysEndDate]))

I need an expression that will subtract the Lost Days from the Restricted
Days, when the Lost Days date range is the same (or between) as the
Restricted Days date range. This will yield total Restricted Days.

Example:

Restricted Days were incurred from 02/01/09 to 03/01/09 and Lost Days were
incurred from 01/01/09 to 02/15/09. The overlap occurs from 02/01/09 to
02/15/09.

How can arrive at the Total Restricted Days?

Thanks in advance for your help,

Rene

 

[Steve Sanford]

Hi Rene,

I was going to write a function to do this, but you wanted an expression. I
did it, but if gets loooong.

To get the overlap, you need the latest of the begin dates and the earliest
of the end dates. But if one of the begin dates is null and/or one of the end
dates is null, the result is a nonsense number. So I check to see if the any
of the dates are null and return "ERROR" if there is a null date.

Below is the formula. I woould suggest copying it, pasting it into notepad,
making sure word wrap is off and making it one long line. Then copy it and
paste it into the control source of the text box.

Be prepared......

Total Restricted Days

=IIf(IsNull([MyLostDaysBeginDate]) Or IsNull([MyLostDaysEndDate]) Or
IsNull([MyRestrictedDaysBeginDate]) Or
IsNull([MyRestrictedDaysEndDate]),"ERROR",DateDiff("d",IIf([MyLostDaysBeginDate]>=[MyRestrictedDaysBeginDate],[MyLostDaysBeginDate],[MyRestrictedDaysBeginDate]),IIf([MyLostDaysEndDate]<=[MyRestrictedDaysEndDate],[MyLostDaysEndDate],[MyRestrictedDaysEndDate])))


I also changed your two formulas.

Lost Days

= IIf(IsNull([MyLostDaysBeginDate]) Or
IsNull([MyLostDaysEndDate]),"ERROR",DateDiff("d",[MyLostDaysBeginDate],[MyLostDaysEndDate]))


Restricted Days

= IIf(IsNull([MyRestrictedDaysBeginDate]) Or
IsNull([MyRestrictedDaysEndDate]),"ERROR",DateDiff("d",[MyRestrictedDaysBeginDate],[MyRestrictedDaysEndDate]))



Change ERROR to a 0 (zero), or whatever, if you don't like the "ERROR".


HTH