Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting within a Time Sheet
Can someone help me with this one:
I am using a Excel time sheet with input in time format. I would like a formula that can extract out the minuets and dived it in to three categories: 1-10 minutes and 31-40 minutes conditional formatting in red color 11-20 minutes and 41-50 minutes conditional formatting in yellow color 21-30 minutes and 51-59 minutes conditional formatting in green color. Example: Time 02.08.00 should be stript down to 00.08.00 and then color the cell in red. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting within a Time Sheet
I assume that if minute is 0 it should be green, so effectively 51-60 to be
consistent with the other conditions? For the red, CF/ Formula is/ =AND(MOD(MINUTE(A1),30)=1,MOD(MINUTE(A1),30)<=10) For the yellow, CF/ Formula is/ =AND(MOD(MINUTE(A1),30)=11,MOD(MINUTE(A1),30)<=20 ) For the green, CF/ Formula is/ =OR(MOD(MINUTE(A1),30)=21,MOD(MINUTE(A1),30)=0) A reminder that Excel time format is not 02.08.00, but 02:08:00 Note also that if you do as you ask and work just with the minutes as you have specified, 02:40:30 would be red, and the transition to yellow would be between 02:40:59 and 02:41:00 -- David Biddulph "ZigZak" wrote in message ... Can someone help me with this one: I am using a Excel time sheet with input in time format. I would like a formula that can extract out the minuets and dived it in to three categories: 1-10 minutes and 31-40 minutes conditional formatting in red color 11-20 minutes and 41-50 minutes conditional formatting in yellow color 21-30 minutes and 51-59 minutes conditional formatting in green color. Example: Time 02.08.00 should be stript down to 00.08.00 and then color the cell in red. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting within a Time Sheet
Hi David
Exactly what I need. Thank you. David Biddulph skrev: I assume that if minute is 0 it should be green, so effectively 51-60 to be consistent with the other conditions? For the red, CF/ Formula is/ =AND(MOD(MINUTE(A1),30)=1,MOD(MINUTE(A1),30)<=10) For the yellow, CF/ Formula is/ =AND(MOD(MINUTE(A1),30)=11,MOD(MINUTE(A1),30)<=20 ) For the green, CF/ Formula is/ =OR(MOD(MINUTE(A1),30)=21,MOD(MINUTE(A1),30)=0) A reminder that Excel time format is not 02.08.00, but 02:08:00 Note also that if you do as you ask and work just with the minutes as you have specified, 02:40:30 would be red, and the transition to yellow would be between 02:40:59 and 02:41:00 -- David Biddulph "ZigZak" wrote in message ... Can someone help me with this one: I am using a Excel time sheet with input in time format. I would like a formula that can extract out the minuets and dived it in to three categories: 1-10 minutes and 31-40 minutes conditional formatting in red color 11-20 minutes and 41-50 minutes conditional formatting in yellow color 21-30 minutes and 51-59 minutes conditional formatting in green color. Example: Time 02.08.00 should be stript down to 00.08.00 and then color the cell in red. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting with Time | Excel Worksheet Functions | |||
HELP with conditional formatting and a max time | Excel Worksheet Functions | |||
conditional formatting time | Excel Discussion (Misc queries) | |||
Conditional formatting and time | Excel Worksheet Functions | |||
conditional formatting with time values | Excel Discussion (Misc queries) |