Here’s a relatively quick way to parse a date-time string or any string (regardless of data type in your source) containing date and/or time. It doesn’t matter if the time format is in 12 or 24 hour format, this method works. The time portion of the data can be in HH:MM:SS or HH:MM or similar variants. Your source data could look something like this:
What we want to do is format each event in a specific background/pattern/color for events occurring in AM time-frame, and another background/pattern/color for events in PM time-frame. The final output that I want is shown below:
The Steps
The first step is to identify which rows under Date & Time column are AM events, and which are PM events. To find that, we create a helper column called AM/PM for example, and apply a formula on each value in Date & Time. We divide the time string by 1 and find the remainder. If the remainder is greater than 0.5 then it must be afternoon (because 12:00 divided by 1 yields 0.5), otherwise, it’s morning. With a date-time string in cell B205, the formula will look like this: 
Once we drag the formula down to cover the range, we get this:
Now that we have each event tagged as AM or PM, we go to the helper column’s first value and create a New Rule: 
This rule should be based on a formula: 
In the formula, type in the cell reference and its expected value “AM”. Set a background color for when this condition is true. Save it.
Do the same for PM for the same cell is fine. We will need to drag it down later to cover the entire range of events anyway.
Now, drag down the formula to cover all rows in the column containing the formula. However, so far, the formatting applies to only each cell, but we want to actually color the entire row, covering other cells as well.
To achieve this, we’ll need to go back to Conditional Formatting menu and choose Manage Rules…
From there, choose each conditions you saved above, one by one (you should have one for AM, another for PM), and make sure to enter the range you want to color in Applies to field.
Save each change. Note: We anchored the column B above because we want to drag down formula to cover all the rows (which we won’t anchor).
After you’re done, you have the rows colored as you chose based on AM/PM event. I chose gradient pattern and then I could hide the helper column if I wish. The final output looks as below:
We’re done.
If you like more info or actual Excel file to learn/use, contact me via this form or directly by emailing me at trseattle at outlook dot com. To support this voluntary work, you can also donate via the Home page.
