Compare two columns in Excel 2003

February 16, 2011 at 10:53:24
Specs: Windows XP
I am trying to compare two columns using Excel 2003. Both columns are dates along with times. I am trying to determine if the date in the second column is greater than 5 days compared to the first column.

Example:
A B
1/1/2011 12:00 1/12/2011 17:30

Is there a way to highlight this row because column B is more than 5 days?

Thanks!

J


See More: Compare two columns in Excel 2003

Report •


#1
February 16, 2011 at 11:20:44
Use Conditional Formatting:

If your data looks like this:

          A                   B                 
1) 1/1/2011 12:00     1/12/2011 17:30

1st – Select cell range: A1 to B1 or A1 to X1

Then, on the Menu Bar:

2nd - Select Format
3rd - Conditional Formatting
4th – Change “Cell Value is” to “Formula Is”
5th – Enter the formula:

=IF($B$1>=$A$1+5,TRUE,FALSE)

Sub Menu Format:
6th - Patterns
7th - Choose your pretty color
8th - Press OK

MIKE

http://www.skeptic.com/


Report •

#2
February 16, 2011 at 11:58:19
Thanks for the response!

I've tried this several different ways, but still can't get it to work. With the above formula, I changed my actual columns and tried selecting the range with no success. I think my logic here is off. I am used to adding the Conditional Format in one cell and then using the paste special option, selecting format.

I think I am messing up the 1st step and the formula for my range.



Report •

#3
February 16, 2011 at 12:41:13
Show me what your formula looks like -
there is a reason for the Dollar Signs ( $A$1 ) in the formula.
They are used to anchor the formula.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
February 16, 2011 at 12:48:44
I used:

=IF($D$13>=$B$13+5,TRUE,FALSE)

The date and time start on columns D and B and the rows are in the 1000s. I added that formula and used the paste special feature, selecting formats, but nothing happens.

Thanks again,

John


Report •

#5
February 16, 2011 at 13:06:57
if the date in the second column is greater than 5 days compared to the first column.

So using your example date:

$D$13 = 1/12/2011 17:30

$B$13 = 1/1/2011 12:00

I first highlighted cells A13 through P13.

And using your formula: =IF($D$13>=$B$13+5,TRUE,FALSE)
it worked for me.

I highlighted cells A13 through P13.

MIKE

http://www.skeptic.com/


Report •


Ask Question