Link to home
Start Free TrialLog in
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
Avatar of Eirman
Eirman
Flag of Ireland image

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
Avatar of agwalsh
agwalsh

ASKER

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
Avatar of [ fanpages ]
[ fanpages ]

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of agwalsh

ASKER

@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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of agwalsh

ASKER

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

ASKER

Totally solved the problem. Thank you :-)
You're very welcome.

Good luck with the rest of your project.
Avatar of agwalsh

ASKER

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.
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.
Avatar of agwalsh

ASKER

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