Good evening all! I sure hope that one of you Excel wizards can help me with this one.
I am trying to establish conditional formatting that will highlight some cells red if the value is greater than 07:00
Below is column of cells to be formatted. These are all times for drive through averages at a fast food restaurant.
I want to automatically highlight any cell with a value greater than 07:00 minutes.
07:49
07:29
08:00
05:48
07:43
07:10
Any help would be greatly appreciated !!!
Apply this rule to the cell if > 0.00486 (this is what fraction of a day 7 minutes is 7/1440)
For cell A2
=A2>7/(24*60)
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Hi, welcome to the forum
What you need to understand about dates and times in excel is…
a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Sun 18 Nov 2018) is actually 43422
Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75
So, based on that, 7 minutes =7/60/24 which calcs to 0.00486111111111111
I would therefor set your CF rule to be >= 0.00486
Give that a shot and let me know how you make out?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Unfortunately none of those fixes worked.
I jused all of the suggestions and the result was either nothing happened at all
or all of the cells in the column were highlighted even the 5:48 which the conditional
format that I want programed is that any cell with a value greater than 07:00 will be
red.
07:49
07:29
08:00
05:48
07:43
07:10
Thank you very much for the follow up and suggestions. Any other ideas?
Check you are actually formatted for mm:ss and not hh:mm
hi kersplash, I am not sure how to perform that task
Or try entering time like this;
00:07:49
Good morning Kersplash!
Maybe I am not doing the right thing at all.
Here is step by step of what I am doing
#1. Highlighting the column of cells
#2. Clicking on Conditional Formattting icon
#3. Choosing "new rule"
#4. Choosing "use a formula to determine which cells to format"
#5. typing in the proposed formula into the box named "format values where formula is true"
#6. Selecting my fill color and my font color
#7. Clicking on "Format"
Which is the range you want to CF.
Good morning, below is the column that I am trying to CF so that anything greater than 07:00 is shaded red.
Any help would be much appreciated!
Where is the file.
Attached
trying to do CF in rows 54 through 59 and columns E through AJ
Anything 07:00 or greater in red with white font
Anything 05:00 or lesser in yellow with black font
As the values in the file are both text and hours:minutes the formulas for CF are:
Red/White: =VALUE(E54)>7/24
Yellow/Black: =VALUE(E54)<5/24
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
JeteMc You are indeed a "Guru" you solved my problem !!!
Thank you!!!
You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks