Can a conditional format be turned into a text outcome?-CodePudding

I want to be able to indicate whether a text is either referring to an AM or PM slot - at the moment, I have conditional formatting in place 'if text contains' - this is because the text only has some similarities in each cell but also contains a date that changes.

I want to be able to mark whether the session is AM or PM in a separate column - an example of what is placed in column C:
Breakfast Club & Afterschool Club (2022/2023) - Thu, 08 Sep 2022, 07:45-08:30 session interval.
The only part that remains the same is the time at the end.

I've included an image to show the type of text that is populated in column E - in column K, I'd like for all yellow slots to be identified as AM and blue as PM - but as you can see there's variation across days.

CodePudding user response：

You asked for:

in column K, I'd like for all yellow slows to be identified as AM and blue as PM

Use the following formula in your Code column.

=INDEX(IF(A2:A="",,IF((MID(RIGHT(A2:A,28),1,2)*1<=11)*(A2:A<>0),"AM","PM")))

(Do adjust the formula according to your ranges and locale)