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

Here is another link just in case

https://we.tl/t-ODeZPIJE50

Regards

Jeovany

Answer 3

Hi Jeovany,

Thank you so much for all your help and hard work.   Unfortunately that is not what I require.   Who have caused the cell background colours to change in the range C2:I5 (Version2 Worksheet).   Instead, I only require the cell backgrounds of B2:B5 to change colour.  

Example:  

Cell B2 - Background Colour = RGB 64 64 64

Cell B2 (Event Start Time) = 25/08/2020 23:30:00

10 Minutes Before Event Start = 25/08/2020 23:20:00 - Cell B2 Background Change Colour = RGB 255 255 0

Event Start Time = 25/08/2020 23:30:00 - Cell B2 Background Change Colour = RGB 0 176 80

35 Minutes After Event Start = 26/08/2020 00:05:00 - Cell B2 Background Change Colour = RGB 198 89 17

etc....

Also, the event start times that are contained within the Column B Cells are acquired through an external data query.  They are not supplied with a date stamp; is there a way that I can add a date stamp to each cell in B2:B3002.   There can be as many as 3,000 events on the worksheet.

I hope I have been able to explain myself a little better.

Regards

Marcus

Answer 4

Thank you for trying to help, I appreciate it very much.   I'm sorry if I didn't explain my requirements very well.

Regards

Marcus

Answer 5

Jeovany,

Thank you so much for your help.   I understood that I had to add the date to the event start time in order to make this work.   I have now managed to add the date through the power query table.  I have now managed to get it all working the way I require.

Thank you again, I could not have done it without your help.

Kindest regards,

Marcus