We can highlight earliest events based on a booking date column in Google Sheets. Normally, events are entered as text strings and may not be sorted. To highlight events, it makes a date or timestamp column mandatory.
Let’s learn to code/write the conditional format rule (custom formula) for this.
Earlier we have learned the tips to lookup or extract earliest dates. Here are those relevant guides.
- Lookup Earliest Dates in Google Sheets in a List of Items.
- Extract the Earliest or Latest Record in Each Category Based on Timestamp.
This post follows a similar approach adopted in the above two guides. But the same formulas won’t work in conditional formatting. It requires some changes.
To highlight earliest events based on a date column, we can follow the below approach/logic.
Logic:
The logic here is; first, we must extract the earliest events based on the date column. Then find the row numbers of the extracted events.
In concise extract the row numbers of the earliest events and highlight the cells matching the row numbers. I’ll explain it in detail (step-by-step) below.
Custom Formula to Highlight Earliest Events Based on Dates
I’m going to write the format rule step-by-step. That means, unless you skip, you will only get the formula for highlighting the events in the last step. So that you can learn the formula and adapt it to your records (datasets). Here we go!
Sample Data and Expected Results:
As you can see in the example, the earliest events in column A are highlighted.
For example, change the DateTime in cell B11 to 22/07/2020 10:00:00. Then the cell A11 will be highlighted instead of cell A2.
Sorting Events in Ascending Order Based on Dates
This is the first step. I mean you should consider each subtitle below as individual steps. Here in the first step let’s use the SORT function.
We should bring the earliest events to the top based on the booking date. We can achieve this by sorting the “Event” column A in ascending order and “Commence” column B also in ascending order.
STEP 1 FORMULA
=sort($A$2:$A,$A$2:$A,1,$B$2:$B,1)For your reference, I am going to enter the above Sort formula in cell D2. We will modify this formula in each step.
Finding Row Numbers of Earliest Events
Since we want to only highlight the earliest events we can’t go ahead with event name matching in conditional formatting.
The reason, there may be (of course there are in the example) multiple events in the same name on the same or different date/DateTime.
The solution is to find the row numbers of the earliest events based on the date column. As per the above sample data, row # 2, 6, and 12 contain the earliest events.
As per our sample data, to find the row numbers of the earliest events in a column in Google Sheets, modify the D2 formula as below.
=sort({row(A2:A),$A$2:$A},$A$2:$A,1,$B$2:$B,1)It would return row numbers in entire D2:D and event names in E2:E12. To limit row numbers only against the events, use IF logical inside of the Sort formula.
STEP 2 FORMULA
=sort({if($B$2:$B<>"",row($A$2:$A),""),$A$2:$A},$A$2:$A,1,$B$2:$B,1)
Now the earliest events are on the top in the list in D2:E.
Remove Duplicate Events to Extract Earliest Events Only
We want to highlight the earliest events based on the date column, right? But the formula doesn’t return any dates!
Don’t worry! Since we have sorted the data the earliest events are on the top. Further, we have row numbers in the output.
Just remove the duplicate events and then extract the row numbers as now we just need to highlight the events based on the row numbers instead of dates.
For that, in the above output in D2:E, we need to unique E2:E. Is this possible?
Yes! We can do that using the function named SORTN.
STEP 3 FORMULA
=sortn(sort({if($B$2:$B<>"",row($A$2:$A),""),$A$2:$A},$A$2:$A,1,$B$2:$B,1),9^9,2,2,1)
I know, some of you are not familiar with the elements used in the SORTN above. I’m not detailing that here. Please read the below two SORTN guides.
- How to Use SORTN Function in Google Sheets to Extract Sorted N Rows.
- SORTN Tie Modes in Google Sheets – The Four Tiebreakers.
Still having doubt? Please let me know. You may use the comment form below the post.
Highlighting Row Numbers of the Earliest Events
Step # 3 formula returns a two-column output. We want the first column to use in conditional formatting, i.e. the first column that contains row numbers.
The below Array_Constrain can return the first column from the SORTN (step # 3) formula result.
STEP 4 FORMULA
=array_constrain(sortn(sort({if($B$2:$B<>"",row($A$2:$A),""),$A$2:$A},$A$2:$A,1,$B$2:$B,1),9^9,2,2,1),9^9,1)Note: In the above two formulas you can see the element 9^9. It’s actually used as an arbitrarily large number.
We can use a Regexmatch formula in the following syntax in conditional formatting to highlit the earliest events.
=regexmatch(row(A2)&"","^2$|^6$|^12$")In this, we should replace "^2$|^6$|^12$" with our step # 4 formula.
But before that use Textjoin with step # 4 formula to get the row numbers in the above ‘regular expression’ format. Here is the modified step # 4 formula.
="^"&textjoin("$|^",true,array_constrain(sortn(sort({if($B$2:$B<>"",row($A$2:$A),""),$A$2:$A},$A$2:$A,1,$B$2:$B,1),9^9,2,2,1),9^9,1))&"$"FINAL FORMULA
=regexmatch(row(A2)&"","^"&textjoin("$|^",true,array_constrain(sortn(sort({if($B$2:$B<>"",row($A$2:$A),""),$A$2:$A},$A$2:$A,1,$B$2:$B,1),9^9,2,2,1),9^9,1))&"$")If you insert the above Regexmatch formula in cell D2 and drag down until cell D12, you will get a bunch of TRUE or FALSE values.
The TRUE values will be in the cell D2, D6, and D12. That means in row # 2, 6, and 12.
To highlight earliest events based on the date column in Google Sheets, we don’t need to insert any formula in any cell.
So please make column D empty. Then just insert the above formula in conditional formatting.
For that first select A2:A or any column in the range A2:C that you want to highlight (I have selected A2:A12).
Then click the menu item Format > Conditional formatting. Select “Custom formula is” under the “Format rules” (inside the contextual sidebar panel).
In the blank field below that, insert the above final formula and click “Done”.
Related:
