Date Comparison - Excel XP

  • Thread starter Thread starter Shawn
  • Start date Start date
S

Shawn

I am attempting to compare a range of dates. I want to
see if 9 dates are at any one time within 90 days of one
another, either earlier or later. If any date is within
90 of any of the other dates, I would like it to flag
that date, or at the very least, confirm that "Yes" one
of these dates is within all the others. The dates are
not in chronological order. I know this is going to be an
array function. I was wondering if this could be done
with Conditional Formatting - can you do array functions
in Conditional Formatting?
....I originally was thinking I would need the Large and
Small functions (to put the dates in Chrono. order), and
would have to convert dates to serial numbers, and then I
started confusing myself...
Any ideas for formula for computing this? Please ask if
you have any questions or need clarification.
Thanks in advance,
Shawn
 
Shawn,

Assume your nine dates are in A1:A9.
In cell B1, use this array formula will give the count of dates withing 90
days of the date in A1. Enter it with Ctrl-Shift-Enter: Excel will enclose
it in { }'s.

=SUM(IF(ABS($A$1:$A$9-A1)<90,1,0))-1

copied down to B2:B9 will give the count for all nine dates, the result you
want.

You may want
=SUM(IF(ABS($A$1:$A$9-A1)<=90,1,0))-1
- I wasn't sure what 'within' means to you.

If you just want a YES or NO, then use
=IF((SUM(IF(ABS($A$1:$A$9-A1)<90,1,0))-1)>0,"YES","NO")

Again, you may want to use <=90.

HTH,
Bernie
MS Excel MVP
 
Bernie,
Thank you very much - this works wonderfully. I am not
sure I understand the formula (ABS converts a date into a
serial number perhaps???), why it is a SUM function, and
the significance of the -1, but it works great. It is not
important I understand how a car works to get home either.

For one bonus point, what would you do if the cells were
not in consecutive cells, but in fact, scattered around
the spreadsheet? Any way you could do any formatting to
them to signify that one of them is within 90 days of one
of the others? Or any other way of 'flagging' the dates?

Thanks again for a great answer,
Shawn
 
Shawn,

In Excel, dates are simply formatted numbers. Today, 11/3/2003, is
37928, and you would see that if you entered that date in a cell and
formatted it as a number with no decimal places. Noon today would be
37928.5, etc.

If the cells were not in consecutive cells, I would create a range of
consecutive cells that would collect my data for me. For example, if
your dates were in Z10, Q53, and T87, in cell A1, use the link =Z10,
in cell A2, use the link =Q53, and in cell A3, use the link =T87.
Then use the previous formula in cells B1:B3 for the comparison, and
you'll get your answer. You can use reverse links to get the answer
back to near the date cells - in cell Z11, you could use the link =B1,
for example.

HTH,
Bernie
MS Excel MVP
 
Back
Top