I need some help I'm trying to identify the total number of active days for a particular "Event ID" (column H) against the "Date" column (column A) and populate this in the "Number of nights active?" column (Column G).
As a guide to what I am looking for, I have manually populated column G (image below and attachment included) with what results I want inputted but with the use of the correct formula, which I hope you guys can help me with please
For example, with Event ID 211966, the number of nights active is "2" this is because this event ID is active between the dates 03/10/2018 - 05/10/2018 for 2 nights (3rd and 4th).
I also expect certain rows the number of nights active column to remain as 0 if all the dates for a particular Event ID is the same. For example, with Event ID 211819, all the events that occurred are on the same date (01/10/2018), so it should not count anything and return a 0 value, as I only want the formula to do a count for when the dates for given Event ID is a different date (each night = +1 count)
The same also occurs with Event ID 211825, where I have 3 different dates, 01/10/2018, 02/10/2018 and 03/10/2018, so the expected result is 2, as its 2 nights between the 3 dates.
I would really appreciate it if someone could help me with this please? What formula would be best to use? Would it be a MIN/MAX formula or something else totally different?
I am willing to clarify anything if what I have said does not make sense!