Calculate time in hours between problem raised and fixed where multiple criteria are true

[jacarutu]

Hi all,

I have attached some mocked up data to help illustrate the problem.

B:B must be the same Cust_Id
C:C must be the same Prob_Desc
D:D must be 'BLANK'
E:E must be 'NOT BLANK'
When all above are TRUE Calculate hours between Problem being logged and finished.

Constraints;
Same Cust_Id may appear again with different Prob_Desc.
Same Prob_Desc may appear with different Cust_Id.
Same Cust_Id may appear again in the future with the same Prob_Desc.

Can't get my head around it. I can get bits of it to work, but never at the same time.

Mike

[jacarutu]

Hi All,

I have anonomised the real data so i could upload it, hope this helps.

It comes in as a CSV file from the Pharmacy Software and i have deleted all extraneous columns.

The problem is based around reporting on the time it takes for Pharmacy interventions to be actioned.

i.e. Line 5 intervention Raised, Line 10 Intervention Closed.

Patient ID and Description columns must be the same (each patient may have multiple interventions)

The intervention is not cleared until there is text in the outcome column.

I have created some extra columns to do some calcs (Highlighted with Yellow Header).

It would be better if the result (time) appeared on the line where the task finished and not when it was raised.

Not sure what I am doing as i am pulling ideas from several sources and can't get it to work.

It has been suggested that this may be better as VB not Excel (thoughts?).

[jacarutu]

File Attachment