How to Flag First Event Date by Location

Emmy Anderson

I've been researching this for a while and haven't been successful yet, hopeful the community can provide some solutions.

I have a sheet that tracks all events across multiple clients. Each client has multiple locations and each location can have multiple events. I need to be able to flag the first event date for each individual location regardless of how many sessions they are hosting.

The Vendor Event ID is only unique to the location

BravoID is a system generated ID based on when the row was uploaded and not necessarily the order in which events are taking place.

How I manually determine which is the first event: I find earliest event date for that location then find the first start time if the earliest date has more than 1 session on the earliest date.

Below is an example of a client with 2 locations. 1 location with 3 sessions and another with 1 session. I manually check the boxes currently but would love to find a formula that can do it for me. Any ideas?

Bassam Khalil

@Emmy Anderson 

Hope you are fine, we will add the following helper columns and convert the formula to column format formula:

1- Convert Start Time To 24 Format = =IF(RIGHT([Examiner Arrival Time]@row, 2) = "AM", LEFT([Examiner Arrival Time]@row, 8), (VALUE(LEFT([Examiner Arrival Time]@row, 2)) + 12) + ":" + MID([Examiner Arrival Time]@row, 4, 6))

2- Time Value = =ROUND(VALUE(MID([Convert Start Time To 24 Format]@row, 4, 2)) / 60, 2) + VALUE(LEFT([Convert Start Time To 24 Format]@row, 2))

3- Min Time = =MIN(COLLECT([Time Value]:[Time Value], [Vendor Event ID]:[Vendor Event ID], [Vendor Event ID]@row, [Scheduled Event Date]:[Scheduled Event Date], [Min Date]@row))

4- Min Date = =MIN(COLLECT([Scheduled Event Date]:[Scheduled Event Date], [Vendor Event ID]:[Vendor Event ID], [Vendor Event ID]@row))

5- First Event Row = =IF(AND([Scheduled Event Date]@row = [Min Date]@row, [Time Value]@row = [Min Time]@row), 1)

the following screen shot shows the result:

and we will use condetional formating to highlit the checked event

Bassam Khalil

Hi @Emmy Anderson 

Hope you are fine, could you please export your sheet to an Excel file and keep a sample data and send it to my Email and i will try to help you with the formula.

My Email: Bassam.k@mobilproject.it

Emmy Anderson

@Bassam.M Khalil thank you! I just sent you the email with attachment.

Bassam Khalil

@Emmy Anderson 

Hope you are fine, we will add the following helper columns and convert the formula to column format formula:

1- Convert Start Time To 24 Format = =IF(RIGHT([Examiner Arrival Time]@row, 2) = "AM", LEFT([Examiner Arrival Time]@row, 8), (VALUE(LEFT([Examiner Arrival Time]@row, 2)) + 12) + ":" + MID([Examiner Arrival Time]@row, 4, 6))

2- Time Value = =ROUND(VALUE(MID([Convert Start Time To 24 Format]@row, 4, 2)) / 60, 2) + VALUE(LEFT([Convert Start Time To 24 Format]@row, 2))

3- Min Time = =MIN(COLLECT([Time Value]:[Time Value], [Vendor Event ID]:[Vendor Event ID], [Vendor Event ID]@row, [Scheduled Event Date]:[Scheduled Event Date], [Min Date]@row))

4- Min Date = =MIN(COLLECT([Scheduled Event Date]:[Scheduled Event Date], [Vendor Event ID]:[Vendor Event ID], [Vendor Event ID]@row))

5- First Event Row = =IF(AND([Scheduled Event Date]@row = [Min Date]@row, [Time Value]@row = [Min Time]@row), 1)

the following screen shot shows the result:

and we will use condetional formating to highlit the checked event

Bassam Khalil

@Emmy Anderson

I will be happy to help you any time.