conditional formatting using clock time
 

How can I highlight the entire row based on the value of column "E" when
column "E" is a TIME based on the 24hr clock. I need rows with a the time in
column "E" between 3:00 pm (15:00) and 5:59 am (05:59) (evening/night shift)
to be shaded. But I do not want times between 6:00 am (06:00) and 2:59 pm
(14:59) (day shift) to be shaded.

Any help will be appreciated.

conditional formatting using clock time
 

Conditional Formatting

=OR(A1=TIME(15,0,0),A1<=TIME(5,59,0))


"Merna" wrote:

How can I highlight the entire row based on the value of column "E" when
column "E" is a TIME based on the 24hr clock. I need rows with a the time in
column "E" between 3:00 pm (15:00) and 5:59 am (05:59) (evening/night shift)
to be shaded. But I do not want times between 6:00 am (06:00) and 2:59 pm
(14:59) (day shift) to be shaded.

Any help will be appreciated.

conditional formatting using clock time
 

If column E has date as well as time, you may need to alter the suggested
formula to
=OR(MOD(E1,1)=TIME(15,0,0),A1<TIME(6,0,0))
[I've also altered the formula so that times such as 05:59:30 will be
included in the shaded region. I'm asuming that the dividing line is at
06:00, not at 05:59?]
--
David Biddulph

"Teethless mama" wrote in message
...
Conditional Formatting

=OR(A1=TIME(15,0,0),A1<=TIME(5,59,0))


"Merna" wrote:

How can I highlight the entire row based on the value of column "E" when
column "E" is a TIME based on the 24hr clock. I need rows with a the
time in
column "E" between 3:00 pm (15:00) and 5:59 am (05:59) (evening/night
shift)
to be shaded. But I do not want times between 6:00 am (06:00) and 2:59
pm
(14:59) (day shift) to be shaded.

Any help will be appreciated.

conditional formatting using clock time
 

Might be a good idea to test for a time entry:

=AND(ISNUMBER(A1),OR(A1=TIME(15,0,0),A1<=TIME(5,5 9,0)))

Biff

"Teethless mama" wrote in message
...
Conditional Formatting

=OR(A1=TIME(15,0,0),A1<=TIME(5,59,0))


"Merna" wrote:

How can I highlight the entire row based on the value of column "E" when
column "E" is a TIME based on the 24hr clock. I need rows with a the
time in
column "E" between 3:00 pm (15:00) and 5:59 am (05:59) (evening/night
shift)
to be shaded. But I do not want times between 6:00 am (06:00) and 2:59
pm
(14:59) (day shift) to be shaded.

Any help will be appreciated.

conditional formatting using clock time
 

Oops! I changed only one half of the formula.
Also, as the OP wanted the *whole row* formatted, it needs to have an
absolute reference, so it would be
=OR(MOD($E1,1)=TIME(15,0,0),MOD($E1,1)<TIME(6,0,0 ))
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
If column E has date as well as time, you may need to alter the suggested
formula to
=OR(MOD(E1,1)=TIME(15,0,0),A1<TIME(6,0,0))
[I've also altered the formula so that times such as 05:59:30 will be
included in the shaded region. I'm asuming that the dividing line is at
06:00, not at 05:59?]

"Teethless mama" wrote in
message ...
Conditional Formatting

=OR(A1=TIME(15,0,0),A1<=TIME(5,59,0))


"Merna" wrote:

How can I highlight the entire row based on the value of column "E" when
column "E" is a TIME based on the 24hr clock. I need rows with a the
time in
column "E" between 3:00 pm (15:00) and 5:59 am (05:59) (evening/night
shift)
to be shaded. But I do not want times between 6:00 am (06:00) and 2:59
pm
(14:59) (day shift) to be shaded.

Any help will be appreciated.

conditional formatting using clock time
 

Might be a good idea to test for a time entry:
=AND(ISNUMBER(A1),OR(A1=TIME(15,0,0),A1<=TIME(5,5 9,0)))

Without testing for a number:

An empty cell will evaluate as TRUE: A1<=TIME(5,59,0)

A text entry will evaluate as TRUE: A1=TIME(15,0,0)

Biff

"T. Valko" wrote in message
...
Might be a good idea to test for a time entry:

=AND(ISNUMBER(A1),OR(A1=TIME(15,0,0),A1<=TIME(5,5 9,0)))

Biff

"Teethless mama" wrote in
message ...
Conditional Formatting

=OR(A1=TIME(15,0,0),A1<=TIME(5,59,0))


"Merna" wrote:

How can I highlight the entire row based on the value of column "E" when
column "E" is a TIME based on the 24hr clock. I need rows with a the
time in
column "E" between 3:00 pm (15:00) and 5:59 am (05:59) (evening/night
shift)
to be shaded. But I do not want times between 6:00 am (06:00) and 2:59
pm
(14:59) (day shift) to be shaded.

Any help will be appreciated.