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

Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

Answer 3

I'm sorry but I have never shared a file before.   I have done it through FileDropper, I hope that works for you.

This is the link

<a href=http://www.filedropper.com/cftimeexample><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=http://www.filedropper.com >upload files online</a></div>

Thank you

Answer 4

http://www.filedropper.com/cftimeexample

Answer 5

I hope that someone else can solve this, I'm lost. Sorry!