Analysing Dates When Some Fields Contain Null values
Posted on 2004-04-23
I have a survey measuring how patients view their experience in a hospital.
I am trying to measure how long, on average, patients have to wait between being admitted and having surgery.
I calculate this by the following fields:
[Surgery Wait Time]=[Date of Surgery] - [Date of Admission]
[Average Surgery Wait Time] = Average(Surgery Wait Time)
The challenge is that not all patients have had surgery--which produces erratic results (such as -7132828)
I have tried to create a conditional formula, to take out those date having null values, as follows:
[Surgery Wait Time] = If([Date of Surgery="","",[Date of Surgery] - [Date of Admission]
When I try calculating [Average Surgery Wait Time], my report doesn't calculate an average at all--instead, it prints multiple copies of the report, each having different values in the field [Surgery Wait Time]--many of which simply don't make sense.
How can I calculate dates where there are null values??