Excel - Formula for Dates

Asked By Dare on 24-Oct-08 03:30 PM
Hello,

I have four sets of dates that are supposed to occur in order.  They are
formatted as the 3/14/2001 format in Excel.  Column A has a start date,
column B has an intermediate date, column C has another intermediate date,
and column D has an end date.  These are supposed to occur in order, but
necessarily do not.  For example, a correct progression would be 1/31/2008
then 5/16/2008 then 10/1/2008 then 10/1/2011.  Working with the OR function
for these dates it would return a true value, and it does.  However, I need a
function to determine when any of the dates are out of order.  That would be,
for example, the first intermediate date occurring before the start date.
Essentially, I need a function that would look at these four dates and return
a value of false when there are any of them that are out of order.

Thanks!




john replied on 24-Oct-08 03:38 PM
Would this work?
=AND(D2>C2,C2>B2,B2>A2)
--
** John C **
Mike replied on 24-Oct-08 03:41 PM
Maybe

=AND(A1<>"",B1>A1,C1>B1,D1>C1)

Mike
Dare replied on 24-Oct-08 03:50 PM
Hi John,

For these dates, 03/12/2007	09/19/2007	01/12/2008	01/11/2011
which occur in order, the AND function returns FALSE, but it should be true.
Is it due to the years spanning muliple years?  How can I correct the
function to make it run more smoothly?  Thanks!
Dare replied on 24-Oct-08 03:53 PM
Mike,

For dates  in order of 03/14/2007	06/12/2007	07/23/2007	07/22/2010, the
formula you gave =AND(A1<>"",B1>A1,C1>B1,D1>C1) returned a value of FALSE,
even though it should be TRUE.  What could be the problem?
Thanks!
john replied on 24-Oct-08 03:58 PM
How are these dates arrived at? Have you checked your regional settings to
ensure you are in the proper date format? If these are 'text' versions, there
may be a problem there. You could try this:
=AND(--D2>--C2,--C2>--B2,--B2>--A2)
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.
Mike replied on 24-Oct-08 04:01 PM
Daren,

Not on my machine it doesn't. I would suggest you check your dates are
really dates and not text that looks like dates.

Try this
=isnumber(a1)
and drag right for b1 etc. All should return TRUE for dates.

Mike
Dare replied on 24-Oct-08 04:11 PM
The dates are formatted as 3/14/2001.  The regional settings are US.  I tried
your formula of =AND(--D2>--C2,--C2>--B2,--B2>--A2), but this did not work
for dates spanning multiple years.  Do you know what might be the issue?

Thanks!
john replied on 24-Oct-08 04:21 PM
Check Mike's second post. Both formulas work for me. Are you in US? Have you
checked your regional settings to ensure US?
Building on Mike's suggestion, you could type this in the cell below your
first date (assuming in A2)
=TEXT(A2,"MMMM DD YYYY") ... and copy across to column D. This should modify
your "dates" like so:
MARCH 12 2007       SEPTEMBER 19 2007       JANUARY 12 2008    JANUARY 11 2011
If not, then there is a problem with how these dates are entered/formulated
in your spreadsheet.
--
** John C **
Ron Rosenfeld replied on 24-Oct-08 04:24 PM
Try:

=AND(SMALL(A1:D1,{1,2,3,4})=A1:D1)

entered with <ctrl><shift><enter> as an array formula.  If you do this
correctly, XL will place braces {...} around the formula.
--ron
Glenn replied on 24-Oct-08 04:27 PM
Another possible solution...with dates in A2:D2 put the following in E2 and
array-enter (CTRL+SHIFT+ENTER):

=SUM(IF(RANK(A2:D2,A2:D2,1)=COLUMN(A2:D2),1,0))=COLUMNS(A2:D2)

This can be expanded to any number of dates just by increasing the ranges or
inserting columns.
Glenn replied on 24-Oct-08 04:36 PM
That's what I was looking for...with a slight change to make it expandable, if
needed:

{=AND(SMALL(A1:D1,COLUMN(A1:D1))=A1:D1)}
Mike replied on 24-Oct-08 04:36 PM
Daren,

The only reason either of the 2 formula you have been given ( and I note you
now have some more) would fail is if your dates are not dates. Check them
again.

Mike
Chip Pearson replied on 24-Oct-08 06:02 PM
Try using the AND function:

=AND(A1<B1,B1<C1,C1<D1)

Adjust the cell references as required, and change the "<" operator to

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Fri, 24 Oct 2008 12:30:01 -0700, Daren
Ron Rosenfeld replied on 24-Oct-08 07:17 PM
There are some other ways to make it "auto-expand":

=AND(SMALL(rng,ROW(INDIRECT("1:"&COLUMNS(rng))))=TRANSPOSE(rng))
--ron
Ashish Mathur replied on 24-Oct-08 07:34 PM
Hi

I get the result as TRUE

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
Ron Rosenfeld replied on 24-Oct-08 08:44 PM
or:

=AND(SMALL(OFFSET(A1,0,0,1,COUNT(1:1)),ROW(INDIRECT("1:"&COUNT(1:1))))=TRANSPOSE(OFFSET(A1,0,0,1,COUNT(1:1))))

--ron
ShaneDevenshir replied on 24-Oct-08 10:32 PM
Might as well add my 2 cents, for a completely different approach without an
array entry

=SUMPRODUCT((A1:C1-B1:D1<0)*(A1:C1>0))=3

or expandable

=SUMPRODUCT(--(A1:C1-B1:D1<0),--(A1:C1>0))=COUNT(A1:C1)

--
Thanks,
Shane Devenshire
ShaneDevenshir replied on 24-Oct-08 10:35 PM
A little simpler still

=SUMPRODUCT(--(A1:C1-B1:D1<0))=3

or

=SUMPRODUCT(--(A1:C1-B1:D1<0))=COUNT(A1:C1)

--
Thanks,
Shane Devenshire