Hi all,
can anybody help with this problem I'm having? I'm trying to condtional format all cells in the attached table over 9:00:00. It's working but it is also highlighting times under 9:00:00 as well?
Any ideas?
Hi all,
can anybody help with this problem I'm having? I'm trying to condtional format all cells in the attached table over 9:00:00. It's working but it is also highlighting times under 9:00:00 as well?
Any ideas?
Hi, welcome to the forum
None of those times are rally times, they are text looking like time. Where is this data coming from?
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
Like I said they are not times, they are text, so you need to convert them 1st.
Instead of the rule you have, use "Use Formula" and use this as your formula...
=--$A1>9/24
Thanks for your reply again.
That formula worked brilliantly on the book attached but when I tried it on the full sheet I'm working on it did nothing?
Are you possibly able to explain the formula so that I could replicate it?
Sure, it is fairly simple.
Your "time" only looks like time, but is actually text. I used a double negative -- to convert it.
Then, instead of adding another function, I just used 9/24.
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 formated in a way that we recognise as a date. So, for instance, today (Thu 04 Feb 2016) is actually 42404
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
If this is not working on your real data...
Did you adjust the cell refs in the CF?
Did you apply it to the correct range?
Does your "time" perhaps also include a date?
Can you upload a sample of your actual data - scrubbed, of course
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks