Hi,

I am trying to make a cell go red if the time in it (hh:mm) is not between the time in two other cells. i.e. 23:45 is one cell, 00:15 is another, if the time in a third cell is not within that 30 minute range i want to use conditional formatting to make it go red. Any help would be appreciated!

Donal

BTW I'm using excel 2003

Try this: If your Data looks like this,

and cell C1 is your target cell to change color.A B C 1) 23:45 00:15 XX:XX1st – Select the cell C1

On the Menu Bar:

2nd - Format

3rd - Conditional Formatting

4th – Change “Cell Value is” to “Formula Is”

5th – Enter the formula:=IF(AND(C1<A1,C1>B1),TRUE,FALSE)

Sub Menu Format :

6th - Patterns

7th - Choose Red

8th - Press OK

9th - Press OKMIKE

Thanks Mike,

That worked for the times given but when I go onto different time ranges e.g.02:45 - 04:30 it stopps working.

Any ideas?

Thanks again,

Donal

It does not work because of how Excel stores TIMES In Excel time represents the fractional portion of a 24 hour day.

For example, 6:00 AM is stored as 0.25, or 25% of a 24 hour day,

and, 6PM is stored at 0.75, or 75% percent of a 24 hour day.So your first request was:

A B 1) 23:45 0:15 0.989583333 0.010416667 <--How Excel stores TimeSo this formula worked:

=IF(AND(C1<A1,C1>B1),TRUE,FALSE)

Because C1 had to be BOTH Less the A1 and Greater than B1.

Your new times are:

A B 1) 2:45 4:30 0.114583333 0.1875 <--How Excel stores TimeSo we can use this:

=IF(OR(C1<A1,C1>B1),TRUE,FALSE)

Because C1 can be Less than A1 OR greater than B1.

MIKE

Ask Your Question

Weekly Poll