Calculating complaints more than 10 days old

[Craig]

Hi All,

I have a complaints database with two columns saying opened and closed
(Column A says Opened and Column B says closed). In each of these columns
there is a date giving the opened and closed date. I need to identify all
complaints that have been open for more than 10 working days on a weekly
basis to identify trends.
Obviously for weeks one and two (based on the normal annual calendar) there
wouldn't be any because they wouldn't have been open long enough but for week
4 any from the 1st week that are still open would be more than ten days old.
So for week 5 i would count any from weeks 1 and 2 that were still open, for
week 6 and from weeks 1-3 that were still open etc etc.

Hope this makes sense.

Cheers

 

[Sandy Mann]

You don't say how you want to identify complaints over 10 working days old.
One way in another column enter:

=IF(IF(B2-A2<0,TODAY()-A2,B2-A2)>14,"Over 10 days","")

If you want to use Conditional formatting to colour the cell then use the
formula:

=IF(B2-A2<0,TODAY()-A2,B2-A2)>14

in the Conditional formatting dialog box and select a Pattern colour to your
taste.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk