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

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History