Highlighting date/time cells below certain times

[brianfitz]

Hi guys, hope someone can help.

I have a massive amount of data on a few spreadsheets and need to highlight increased call volumes at certain peak times, in this case between 10am to 11am and 2pm to 3pm.

My datetime columns format is 23/06/2010 10:00 and was wondering is there conditional formatting I could do that could highlight all times in the above range in a certain colour?

Any help is much appreciated,

Brian

 

[xld]

Use a CF formula of

=OR(AND(ROUND(MOD(A2,1)*86400,0)>=ROUND(--"10:00:00"*86400,0),ROUND(MOD(A2,1)*86400,0)<=ROUND(--"10:00:00"*86400,0)),
AND(ROUND(MOD(A2,1)*86400,0)>=ROUND(--"14:00:00"*86400,0),ROUND(MOD(A2,1)*86400,0)<=ROUND(--"15:00:00"*86400,0)))

 

[p45cal]

=or(and(mod(d4,1)>=timevalue("10:00"),mod(d4,1)<=timevalue("11:00")),and(mod(d4,1)>=timevalue("14:00"),mod(d4,1)<=timevalue("15:00")))
with date-timestamp in D4

 

[brianfitz]

That seems to have done the job lovely, guys much appreciated for your help and time.