Conditional formatting based on time due
Hi,
I just want to format a table where in the status will turn green if the time on one cell is less than an hour, then yellow from 1 hour to 2 hrs, then red if beyond 2 hours.
For example on cell A3 time entered is 1:30:00 AM (that's the time it was entered as well, using ctrl+shift+another cell (status) should turn green on the time of entry then after an hour it should turn yellow then after 2 hours it should turn red
Thank you for your help.
=vlookup(mod($a$1,1)-a3,$f$3:$g$5,2)=1
Select A3
Conditional Formatting
New Rule
Use a formula to determine...
=AND(A3>MOD(NOW(),1),A3<=MOD(NOW(),1)+1/24)
format as green
=AND(A3>MOD(NOW(),1),A3<=MOD(NOW(),1)+2/24)
format as yellow
=AND(A3>MOD(NOW(),1),A3>MOD(NOW(),1)+2/24)
format as red
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Hi Special-K
I tried your formula but it seems like it's not working, can you please check the attached file?
The cell I want to change color is the status then it will based on the time in row C
Sample.xlsm
Oops! Looks like the formulas shoudl be
=AND(C3>=MOD(J2,1),C3<=MOD(J2,1)+1/24)
=AND(C3>=MOD(J2,1)+1/24,C3<=MOD(J2,1)+2/24)
=(C3>=MOD(J2,1)+2/24)
Tried it, but it's only displaying red on all status. Do I have to change the order?
Sample.xlsm
Sorry, there was no way to test the time of NOW() without waiting two hours so I replaced NOW() with J2 and put a time in J2. Forgot to put the NOW() back.
Try these
=AND(C3>=MOD(NOW(),1),C3<=MOD(NOW(),1)+1/24)
=AND(C3>=MOD(NOW(),1)+1/24,C3<=MOD(NOW(),1)+2/24)
=(C3>=MOD(NOW(),1)+2/24)
I actually noticed that earlier and changed it to NOW() but it's still not working. Is it because NOW() is date and time and C3 has time only?
No, the MOD() takes care of that by removing the date
Can you post the file it's not working on, it looks like it's working for me
Here's the file.. Thank you for your help.
This file works fine
The time here in the UK is 13:15 PM
If I change C3 to 13:30 it turns green since its in the next hour
If I change C3 to 14:30 it turns yellow since its between 1-2 hours
If I change C3 to 16:30 it turns red since it's over 2 hrs away
Whatever time it is in the Phillipines enter a time in C3 less than an hour ahead
Then enter a time 1-2 hours ahead
Then enter a time over 2 hrs ahead
The result should be as the example I ran in the UK, ie green yellow red
This only works for rows 4 to 6 since the other rows dont have any conditional formatting.
Just to describe it further..
once you enter a number on A3, B3 and C3 will be filled automatically with current date and time, the STATUS cell should turn green right away since it's within the hour, then after an hour the STATUS should turn yellow, then after 2 hours it should turn red and stay that way unless the entry was deleted.
I really hope this is possible and thank you very much for your time.
Mm, thats the first time you've mentioned anything being entered into B3 and C3 automatically.
Change the formulas to this
=AND(A3<>"",C3<=MOD(NOW(),1)+1/24)
=AND(A3<>"",C3>=MOD(NOW(),1)+1/24,C3<=MOD(NOW(),1)+2/24)
=AND(A3<>",C3>=MOD(NOW(),1)+2/24)
Sorry about that, I just that found the code on another thread, when I started the thread the data is being entered manually.
Tried the new formula but it turned everything to green.
Current time here is 8:52PM
H3 should turn red, H4 should turn yellow, H5 should be green.
Table6.JPG
Hang on,
You enter a ref no. in A3, B3 date gets entered automatically along with C3 which is the current time.
The condtional formatting then updates as it's looking at C3. It sets it to green since it's within an hour.
In order for the green to turn to yellow codntional formatting would have to work in real time
It doesn't.
It's 14:25pm here. I just set up a CF with =MINUTE(MOD(NOW(),1)<27 with a format of green.
Cell turned green since 25 < 27.
If CF works in real time at 14:27 it should change from green and it didnt.
In order for your spreadsheet to reflect the colours you want after a specifed tiem you'll have to keep hitting F9 to refresh the sheet every so often.
I thinkyou need a VBA solution of which Im no expert.
It's ok to refresh the sheet, this sheet will be updated every 5 minutes or less.
is it ok to use:
=MINUTE(MOD(NOW(),1)<59 for green?
=HOUR(MOD(NOW(),1)>1 for yellow?
=HOUR(MOD(NOW(),1)>2 for red?
Update:
Tried it, didn't work. I was hoping there's just a formula that I can use. Not familiar with VB as well.
Last edited by maeayanami; 09-13-2017 at 10:17 AM.
If you're going to refresh the sheet then use a cell for =NOW(), e.g. J2
That way you can test if the following formulas work by altering the value in J2 rather than waiting two hours for them to change.
These formulas should work but this is what I had in post #5
=AND(A3<>"",C3<=MOD(J2,1)+1/24)
=AND(A3<>"",C3>=MOD(J2+1/24,C3<=MOD(J2,1)+2/24))
=AND(A3<>"",C3>=MOD(J2,1)+2/24)
Hi Special-K,
I actually tried it by waiting for two hours and it didn't change colors. All entries stayed green. It was actually working when I use J2 and change value from there. Maybe I really need VBA for this one, I will keep on searching and hopefully find a solution. Thank you for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks