conditional formatting referencing times

hi
I've set up a sheet which has conditional formatting based on the begin time and end time chosen. The formatting works fine for begin dates but it doesn't go right up to the end time. What am I missing?
Rota---vers-01.xlsx
LVL 1
agwalshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EirmanChief Operations ManagerCommented:
It's something to do with formatting of the times in row 3
Delete 17:30:00 and enter 17:30 in AD3 and it works

Your formula is fine



EDIT: Just change the format to hh:mm:ss for the times in row 3
0
agwalshAuthor Commented:
hi
I've tried that and the first time I changed it, it worked fine. Then I tested it again and I've still got the same recurring problem. See attached file. As you say I reckon it is something to do with the formatting of the times but it's as if I need to tweak the formatting that affects the 17:30 bit...any suggestions?
EE-Time-Rota-02.xlsm
0
[ fanpages ]IT Services ConsultantCommented:
Hi,

I see that there is an issue with the way in which the time '18:00:00' was entered in cell [AE3].

I rectified this by entering the following formula into cell [G3]
=TIME(HOUR(F3),MINUTE(F3)+30,0)

I then copied [G3] across the same row to cell [AK3].

Please see the attached example.

BFN,

fp.
Q-28233378.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
check the formula and the rule in attached result file.

Formula used is:
=IF(AND(HOUR(Q$3)<=HOUR($D$4),HOUR(Q$3)>=HOUR($C$4),MINUTE(Q$3)<=MINUTE($D$4),MINUTE(Q$3)>=MINUTE($C$4),SECOND(Q$3)<=SECOND($D$4),SECOND(Q$3)<=SECOND($C$4))," ","")

Formula checks whether the time lies in between start and end times entered in the cells C4 and D4.

Conditional formatting is based on whether the cell has the " " or "" values.  

ie conditional formatting is used based on whether the cell value is space or null.
EE-Time-Rota-02---Final.xlsx
0
agwalshAuthor Commented:
@fanpages - brilliant - absolutely love it. One small thing..what do I need to do to tweak it so that it appears in the 24 hour format? (at the moment, it's doing the AM/PM thing)
0
[ fanpages ]IT Services ConsultantCommented:
Really?  I see the format [hh:mm:ss]!

May I suggest that you simply select the range [F3:AK3], use the [CTRL]+[1] key combination, set the required (custom) format, & confirm with the [OK] button?

Please let me know if this works &, if not, what you then see after setting the custom format to [hh:mm:ss].

Thanks.
0
agwalshAuthor Commented:
@fanpages - yep, tried that and worked beautifully. Thank you :-)
0
agwalshAuthor Commented:
Totally solved the problem. Thank you :-)
0
[ fanpages ]IT Services ConsultantCommented:
You're very welcome.

Good luck with the rest of your project.
0
agwalshAuthor Commented:
hi
I didn't allocate any points to his solution because I didn't use it in any way whatsoever. The other answer I got answered my question in the most flexible useable way possible.
0
EirmanChief Operations ManagerCommented:
No hard feelings agwalsh. I thought it was simply an oversight on your part.

If that's the way you see it after having thought about it, I'll cancel my objection.
0
agwalshAuthor Commented:
No, I read every solution and I do think about how to allocate the points. And as I said it was simply because I didn't use your solution at all...no reflection on the quality of it. The other one fulfilled my purposes better. And of course :-) no hard feelings..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.