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.

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.

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.



.