Report Date Calculation

[Johnny Drama]

Hi all,

I've currently got a report that takes two date fields and calculates the number of days between the two dates. The problem I am having is that I need the number of days to be calculated even when the Management Close Date field does not have an actual date entered into it, i.e. it needs to calculate based upon the current day until a date is enter into the field. The current calculation below works fine, but I'm not sure where to insert an =now() statement...should it be in this string or should it be a part of the Management Close Date field?

=IIf(IsNull([Opened Date]),DateDiff("d",[Management Close Date],Date()),DateDiff("d",[Opened Date],[Management Close Date]))

Thanks

 

[CBrighton]

Just to confirm what you want:

Your current code allows for a null open date, you want to include a different datediff for a null close date too?

Logically this means there are 4 possible scenarios:

1. open null - close not null
2. open null - close null
3. open not null - close not null
4. open not null close null

Before you look at updating the Iif statement you need to have a clear idea of what calculation you want to perform in each instance.

Your current code accounts for scenarios 1 & 3, so it's 2 & 4 which you need to define before you can continue.

 

[Johnny Drama]

I made one slight change, from Opened Date to Mangement Close Date.
=IIf(IsNull([Management Close Date]),DateDiff("d",[Management Close Date],Date()),DateDiff("d",[Opened Date],[Management Close Date]))

I only want two scenarios:
Management Close Date is Null - datediff between Opened Date and Null (which would be now())

Management Close Date not null - datediff between Opened Date and Management Close Date

 

[CBrighton]

In that case:

=IIf(IsNull([Management Close Date]),DateDiff("d",[[B]Opened Date[/B]],Date()),DateDiff("d",[Opened Date],[Management Close Date]))

Your current code says that if the close date is null then compare close date and today's date, which will obviouslly not work.

The above says if closed date is null to compare open & today, else to compare open & close.


You can replace Date() with Now() if you want to include the exact time, but Date() will give you the dd/mm/yyyy (or whatever your localisation is) which I expect is the part that you want to compare.

 

[Johnny Drama]

Figures it was something right in front of my face. Ugh. Thanks much!