Avatar of agwalsh
agwalsh asked on

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
Microsoft OfficeMicrosoft Excel

Avatar of undefined
Last Comment
agwalsh

8/22/2022 - Mon
Eirman

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
ASKER
agwalsh

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
ASKER CERTIFIED SOLUTION
[ fanpages ]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Raheman M. Abdul

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
agwalsh

@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)
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
agwalsh

@fanpages - yep, tried that and worked beautifully. Thank you :-)
ASKER
agwalsh

Totally solved the problem. Thank you :-)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
[ fanpages ]

You're very welcome.

Good luck with the rest of your project.
ASKER
agwalsh

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.
Eirman

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
agwalsh

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..