Calculate total number of days between two dates in a query

[randallst]

Hi Everyone,

Stuck on one bit of a report i'm putting together. Basically I have a report that shows any 'Issues' that wasnt closed within the KPI Target.

I have the report working, but I simply want to do a count of how many days the observations overran the 'Target Date of Closure'.

The report also shows observations that are not closed but passed their Target Date of Closure. These observations will not have a 'Actual Closure Date', but I would like to still have a count of how many days its overrun closure.

This is a formula I tried to piece together but obviously not correct as it's not returning anything;

IIf(IsNull([Issues]![Actual Closure Date]),DateDiff("d",[Issues]![Target Date of Closure],Date()),DateDiff("d",[Issues]![Target Date of Closure],[Issues]![Actual Closure Date]))

Hopefully that actually makes sence and someone might be able to help me.

 

[CJ_London]

An alternative way of writing this is

DateDiff("d",[Issues]![Target Date of Closure],nz([Issues]![Actual Closure Date]),Date()))
I would also trying changing the bangs (!) to dots (.)

If it is still not returning anything, then this implies an error elsewhere in your query

 

[randallst]

I was being an idiot and entering it in the wrong area. Issue solved. Thanks for your reply