Conditional formatting using times

April 20, 2011 at 06:59:51
Specs: Windows XP
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!
BTW I'm using excel 2003

See More: Conditional formatting using times

Report •

April 20, 2011 at 13:33:50
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:XX

1st – 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:


Sub Menu Format :
6th - Patterns
7th - Choose Red
8th - Press OK
9th - Press OK


Report •

April 21, 2011 at 08:20:14
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,

Report •

April 21, 2011 at 12:17:12
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 Time

So this formula worked:


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 Time

So we can use this:


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


Report •

Related Solutions

Ask Question