Conditional Formatting Time of Day

[Emeng]

Hi all

With my spreadsheet, and using the Conditional Formatting tool, I am trying to format times before 7AM & after 7PM to identify them more easily.

I tested my formula in a cell on the sheet to prove it works…

“=IF(OR(TIMEVALUE(B2)<0.291665,TIMEVALUE(B2)>0.791667),1,2)”, and it does, returning 1 & 2 where expected.

Using “=OR(TIMEVALUE(B2)<0.291665,TIMEVALUE(B2)>0.791667)” in the ‘Use a formula…’ dialog box does not work for some reason.

My spreadsheet is generated by SAP, with the times initially formatted as text and I wonder whether that may be a possible reason?

I have tried to reformat using various time formats without success; however the formula entered in the sheet recognises all of them (clear the formula results, change the format, reapply the formula…all good).

I have also tried to format the ‘time’ cell based on the value of the ‘formula’ cell, but no joy.

Attached is a simple part my spreadsheet.

Hoping someone has solved this previously.

Any help is much appreciated,

Regards

Mark

 

[Somendra Misra]

Hi Mark,

See your file.

Regards,

 

[Emeng]

Hi Somendra

Thanks for your reply.

I see that time values are added to D1 & D2 but cannot see where they are referred to by the recorder. The desired results have been achieved but I'm not sure how?

Regards Mark

 

[NARAYANK991]

Hi Mark ,

I am not able to see what your problem is ; if you wish to have a CF rule in column B , which will highlight those values in column B that are earlier than 7 AM or after 7 PM ( booth times inclusive ) , all you have to do is this :

Select the data range B2:B594.

Enter the following formula in the CF custom rule formula box :

=OR($B2<="07:00:00",$B2>="19:00:00")

Not only is this correctly interpreted by Excel , it is easily read and understood by any user ; why use values such as 0.291665 and 0.791667 ?

If you want that the corresponding cells in column A should also be highlighted , use the same rule , but select the range A2:B594 first.

If you do not want the 7 AM or 7 PM times to be highlighted , change the <= and >= signs in the formula to < and > alone.

Narayan

 

[Somendra Misra]

Hi Mark,

I did not used D1 & D2 values, simple used an OR in CF rule. To add waht Narayan Sir said, I will always add Margin Values to a cell and than link those cells in CF rule, so that any time I need to change the value and see the change I just need to change cell value and the change will reflect automatically.

Regards,

 

[Emeng]

Thanks for your replies & explanations gents.

I see I was making things more complicated than they need to be.
I can apply what I've learned here to other procedures & speed up my code.

Many thanks & regards

Mark