Excel Conditional Formatting Time - Midnight Crossover

Question

Hi All,

I have a column of cells that contain various event start times (hh:mm).  

I also have conditional formatting that changes the background colour of each cell according to the current time:

10 Minutes before event start = Yellow

Event Started = Green

Quarter Way = Burnt Orange

Halfway = Light Blue

3 Quater Way = Bronze

Final 20 Minutes = Red

Finished = Black

My issue is that if the start of the event is for example 23:30 today and therefore the end of the event is 01:00 tomorrow, the cell automatically blacks out today (after 01:00) which is before the event has even begun.

I understand there is something that needs to be done to add 1 day etc, but I have been struggling to workout how to do it for days.   Any help would be greatly appreciated.

Thank you.

Answer 1

Hi Marcus

Please, find in the link below a copy of your file with the answer and solution to your question

https://1drv.ms/x/s!AjGRD1TlwpAGmUSKk7uy7jhn-YhD?e=CI0m9K

Notes:

1- The Start time in column B must be entered with the date as a full Date-Time stamp, otherwise Excel doesn't know "1:00:00"  what date belongs to 

2- With the formulas introduced in the range column G to M and the ones used in the Conditional Formatting.

You can prescind from (leave out) the use of all the cells marked within the 3 rectangles 

Unless you need to keep then for some other reasons

Do let me know if you need more help

On the other hand,

If the answer helped you.

Please, consider marking this thread as answered.

It would help others in the community with similar questions or problems.

Thank you in advance

Regards

Jeovany

Answer 2

Please explain what the cells in the formulas contain.

Answer 3

Hi HansV,

Thank you for your reply.

B3 = Current Time

C8 = Event Start Time

AA8 = Start Time -10 Minutes

AB8 = Start Time + 35 Minutes

AD8 = Start Time + 48 Minutes

AE8 = Start Time + 62 Minutes

AF8 = Start Time + 84 Minutes

AG = Start Time + 120 Minutes

Cell AG represents the finish time of the event.

CF Formulas

B3 (Current Time) >= AA8 (10 Mins Before Event Start)  - Yellow

B3 (Current Time) >= B8 (Event Start) - Green

B3 (Current Time) >= AB8 (35 Minutes After Event Start) - Burnt Orange

B3 (Current Time) >= AD8 (48 Minutes After Event Start) - Light Blue

B3 (Current Time) >= AE8 (62 Minutes After Event Start) - Bronze

B3 (Current Time) >= AF8 (84 Minutes After Event Start) - Red

B3 (Current Time) >= AG8 (120 Minutes After Event Start Time) - Black

I am very sorry if I have replied in a bad way, I just wanted to try make it easy for you to understand what I have done so far.

Thank you so much for wanting to help me.

Answer 4

Use formulas such as

=$B$3+($B$3<AA8)>AA8

=$B$3+($B$3<B8)<B8

=$B$3+($B$3<AB8)<AB8

etc.

Answer 5

Thank you HansV,

Unfortunately your suggestion does not work, but thank you very much for all your help and for replying so quickly.

Regards,

75Marcus