Highlight cells to show time has passed, will pass soon, and has ample time

[cbetsinger]

First, let me thank you for helping me to solve this question. I hope im posting it in the correct forum.

What I want to do is have a cell highlighted like a stop light, based on promise time, versus the current time. I attached a sample to this post.

The idea is to know if a promise time, given to a guest, will be met. If it will not be met, then we can inform our guest for damage control.

Highlight RED, time has exceeded/passed PROMISED TIME.
Highlight YELLOW, time is about to exceed/pass PROMISED TIME. (<59 minutes till promise time)
Highlight GREEN, >1 hour TILL PROMISED TIME.

Id like to understand how you came up with the formula if you could explain it to me. That way I can adjust the highlights as my needs change. In the future I may want an additional color to inform me of <30 minutes to promise time so I can prepare for a possible failure.

For the YELLOW highlight id like it to be 59 minutes till promise time. This way I know when I have less than one hour to keep our promise.

Finally can I have this shared in my department so anyone can view it from their terminal. We have shared drives that I could have this sheet stored. Will it be "live" as I update it when each guest comes in? If not how can I make it live?

[Kenneth Hobson]

Welcome to the forum!

I guess you could sort of do it by Conditional Formatting. Howsoever, keep in mind that like most all functions, they are Volatile. This means that a calculation event has to occur before formulas can update. You can force calculation.

Before you add the formula, put this one into some cell that your formula will refer to absolutely. =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
The reason is that now()<>that formula even though it might look like it does by numeric date format. Now()=Number of days from the fractional part of the day at the time of calculation. Dates and Times are in days or decimal day parts since the first date approximately 1900.

Of course at the start of each day, most of your cells will show Green depending on your data and time you first calculate/open the file.

So, time, is actually a fraction of a day. 12:00:00 AM would be 0 of course. 1 minute is 60 seconds. =24*60*60=86400 is how many seconds per day. This means that 60/86400 = 12:01:00 AM. Now you know the math.

Do your 3 formulas as usual before trying them in the Conditional Format formulas. You can easily change a comparison's cell time value to see if you have the math right. e.g. 59 minutes = 59*60 seconds or 56*60/86400 days.

[alansidman]

I have set this code to run every 2 minutes. You can adjust as you deem appropriate, ie. 5 minutes, etc.

Please Login or Register  to view this content.

[cbetsinger]

I added the VBA to my workbook, but the promise times are all red no matter what time is put into the promise cells.

Im not sure what is wrong with the workbook. I attached it to this post if that helps.

[cbetsinger]

Welcome to the forum!

I guess you could sort of do it by Conditional Formatting. Howsoever, keep in mind that like most all functions, they are Volatile. This means that a calculation event has to occur before formulas can update. You can force calculation.

Before you add the formula, put this one into some cell that your formula will refer to absolutely. =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
The reason is that now()<>that formula even though it might look like it does by numeric date format. Now()=Number of days from the fractional part of the day at the time of calculation. Dates and Times are in days or decimal day parts since the first date approximately 1900.

Of course at the start of each day, most of your cells will show Green depending on your data and time you first calculate/open the file.

So, time, is actually a fraction of a day. 12:00:00 AM would be 0 of course. 1 minute is 60 seconds. =24*60*60=86400 is how many seconds per day. This means that 60/86400 = 12:01:00 AM. Now you know the math.

Do your 3 formulas as usual before trying them in the Conditional Format formulas. You can easily change a comparison's cell time value to see if you have the math right. e.g. 59 minutes = 59*60 seconds or 56*60/86400 days.

In L6 I have the =now() running so we (my team) know the current time. Id like the colors of the I Column or Promise Time to change as the time posted in that cell nears L6 (current time). As the times in I column get closer and closer to L6. Id like them to change based on the rules... passed, <1 hour, >1 hour.

Its supposed to look like an airport flight screen.

[alansidman]

I'm in the US Mountain Time zone. I changed the times as they had all passed and turned up red. Here is my copy of your sheet that worked for me.

[cbetsinger]

I attached 2 screen shots. I opened up the file you saved. But the promise time cells still do not highlight correctly. The second screenshot shows how the layout should look as the sheet gets updated.

The file I opened from you, the 8am should be the only promise time that's red. 930 should be green all the way to 11am. I manually highlighted the cells in the second picture to show what im trying to accomplish.

Thank you for all of your time and help. When this sheet starts working, I wouldn't be able to thank you enough.

[cbetsinger]

If it helps here is another screen shot that I manually highlighted. This is what I'm after, all the highlights are based on my current time and what I promised.

[alansidman]

I don't know what to tell you. I used your file that you sent. I changed the times so that some would be green, red and yellow. They all worked perfectly as you had requested. FYI. Pictures are of little value in analysis as one cannot determine what you are doing or not doing in the Excel sheets. Are you getting any errors when you run the macro? Have you placed it as a module? Have you made any changes to the one provided? Are your times formatted correctly? I'm just digging here.

[cbetsinger]

I didn't set the Macro to run with Hot Keys... this is working like a DREAM. I cannot thank you enough!!! I have a lot to learn. I will pay it forward.

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

You are very welcome. Glad you got it working.

[cbetsinger]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

You are very welcome. Glad you got it working.

DONE, PROBLEM SOLVED... Mahalo to all that helped.