Cell color changing based on Time passed

Hi,

I am developing a new Unit at work and I need to be able to highlight how long a call has been with us.

Basically, a call is made by a member of the public and we then arrange for someone to visit. Due to other commitments we may not always be able to keep the original appointment.

I therefore want a cell within an excel spreadsheet to change colour as time goes on. For example for a cell to be green for 2 hours, then amber for 2 hours and then red for 2 hours. The principle being that the visit needs to become a priority if the cell goes red, or we need to call the person back.

Any ideas how I achieve this?

Thanks

Christine

Hi Christine,
Welcome to the forum. Please take a moment to retitle your post as per the forum rules
http://www.excelforum.com/forum-rule...rum-rules.html
This helps the moderators and anyone in the future searching for specific topics.

I'd suggest something like "Cell color changing based on Time passed" Then we will respond to your post.

Thanks

It's hard to be specific without a sample workbook (upload by going advanced> manage attachments).
However, what you want is called conditional Formatting, it's on your home tab of your ribbon.
Let's say your times are in column A beginning with A2
Select A2:Awhatever (as far down as you think it will go)
Conditional Formatting> New Rule > Use Formula

= Hour(MOD(NOW(),1)-A2)>=4 format as Red
Then repeat for
= Hour(MOD(NOW(),1)-A2)<4 format as amber
= Hour(MOD(NOW(),1)-A2)<2 format as green
Go into Conditional Formatting > Manage Rules and check that all have "Stop if True" checked.
See attachment

Okay, had to modify it a little bit because your cells are formatted as text. You didn't upload a copy with how your conditional formatting was entered so I can't see what you were doing wrong. How are people entering time?
3:30, 3:30 PM, 15:30, something else? That may cause problems if people are entering it incorrectly. For example, Excel will see 4:30 or 4:30 AM as a time but will not recognize 4:30AM. Also an entry of 4:30 will be read as 4:30 AM. I suggest you switch to time formatted and use data validation to make sure they are entering it as a number.

In any case, the third conditional formatting rule is now
= AND(LEN(B16)>1,Hour(MOD(NOW(),1)-B16)>=4 so that blank cells don't turn red.
Attached is an example.

Yes, I was assuming that you were entering a time in the FWIN column. Even if they enter a time in a different column, you can change the color of FWIN.

It is possible to do this without actually entering the time but that will require a Visual basic solution which means everyone must have macros enabled when they are working with the spreadsheet. Do you want them to enter a time? or do you want to try Visual Basic?

Here's an updated workbook with some VBA (event driven) which automatically fills in the date and time (I added a column) when someone puts in the FWIN number. Will this work for you? The code is on the worksheet module (right click on the sheet1 tab, view code).

Please Login or Register  to view this content.

I modified the conditional formatting statements to look at column D instead of B.

Hmm, I just rechecked it. Try again. Make sure you're opening Tasking Template2 from post 11 and that "content" is enabled.

Correction. Once you've downloaded template2, I have the Conditional formatting rules in the wrong order. Go to Conditional Formating > manage rules and move the Green to before the yellow (there's a blue arrow at the top that lets you move things around). Let me know if there are issues.

shomibg,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

Yes, just start a new thread titled appropriately (something about conditional formatting dates). Ideally, attach an example spreadsheet so we can see how things are laid out. (Go Advanced>Manage Attachments)

Misplaced post

Click FORUM at the top, select a forum (maybe General), then click Post New Thread

Moving this to a new thread, as I just read the Admin message in the previous posts.

Regret the repeated post.

Regards
JT