Amend formula to take other conditions into account

[chrisellis250]

Hi all,

I have attached a spreadsheet showing how my data looks (which has been redacted).

In columns AI & AJ I have two formulas to look at dormant periods between phone calls, or similar.

The issue I have is that, as shown in columns AL & AM, some activity is not manual and is automated so I am looking to see if my formula could take these conditions into account?

So for example, if column AL says "Data Repeat" or column AM says "Lengthy" then I want the formula to ignore these times, so that the dormant period is longer - so between URN 1 and URN 10 there is a dormant period of 9hrs 45mins, but this isn't picked up in Column AK as "Rest", due to the "Data Repeats" in Column AL (the same would apply if there were any "Lengthys" in AM.

Any ideas?

Many thanks

[Gregb11]

Quick question - are you still on V2010 or have you upgraded?
Also, you mention going back to URN 1. Why URN 1 when URN 2 doesn't say "Data Repeat"?

[chrisellis250]

Hi Gregb11,

Yes still on v2010.

URN 1 is the last manual activation, as URN 2-9 all have the same minutes and seconds (URN 2 isn't shown as "Data Repeat" but should be")

[chrisellis250]

Anyone have any ideas?

I've still not been able to solve my problem.

[Gregb11]

To be clear, in what column(s) are you looking for a formula?
Are you able to supply a sample file that shows exactly what you want it to look like with the right formula?