Mark events that occurred within specified time of another event with same name

Hello and thanks for reading!

I am looking for a way to assign “chargeable” or “nonchargeable” to a rows on a worksheet based on how close (in hours) one event happened in relation to another matching event, in this case within 2 hours.

For example, If row 1 represents an event named "Setup" and has a time value of 1:00 and subsequent rows with events "Setup" and "Setup" both have a time value of 1:30, I would like to assign a specific value to the first instance of "Setup" to indicate it is chargeable and another value to the second and third instance to indicate they are unchargeable. If later on, a fourth "Setup" event is entered with a time value outside of the 2 hour range of any of the others, then it would be chargeable.

So the output would like something like this.


Event -- Time -- Billable Status
SetUp -- 1:00 -- Chargeable
SetUP -- 1:30 -- NonChargeable
SetUp -- 1:30 -- NonChargeable
SetUp -- 4:30 -- Chargeable


Essentially, the requirement is that a service cannot be billed if it happened within 2 hours of the administration of the same service.

Let me know if that makes any sense and thanks for reading and helping!

Post up a sample workbook and it makes it far simpler for people to test their ideas.

Click GO ADVANCED and use the paperclip icon to post up your workbook.