Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting with Date and time | Excel Discussion (Misc queries) | |||
conditional formatting with time values | Excel Discussion (Misc queries) | |||
can cell's act like time clock at a certen time/date for payments | Excel Discussion (Misc queries) | |||
conditional formatting 24-hour clock | Excel Worksheet Functions | |||
Time Clock | Excel Worksheet Functions |