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!

Would this work?
=AND(D2C2,C2B2,B2A2)
--
** John C **


"Daren" wrote:

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!

Maybe

=AND(A1<"",B1A1,C1B1,D1C1)

Mike

"Daren" wrote:

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!

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!


"John C" wrote:

Would this work?
=AND(D2C2,C2B2,B2A2)
--
** John C **


"Daren" wrote:

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!

Mike,

For dates in order of 03/14/2007 06/12/2007 07/23/2007 07/22/2010, the
formula you gave =AND(A1<"",B1A1,C1B1,D1C1) returned a value of FALSE,
even though it should be TRUE. What could be the problem?
Thanks!

"Mike H" wrote:

Maybe

=AND(A1<"",B1A1,C1B1,D1C1)

Mike

"Daren" wrote:

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!

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.


"Daren" wrote:

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!


"John C" wrote:

Would this work?
=AND(D2C2,C2B2,B2A2)
--
** John C **


"Daren" wrote:

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!

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



"Daren" wrote:

Mike,

For dates in order of 03/14/2007 06/12/2007 07/23/2007 07/22/2010, the
formula you gave =AND(A1<"",B1A1,C1B1,D1C1) returned a value of FALSE,
even though it should be TRUE. What could be the problem?
Thanks!

"Mike H" wrote:

Maybe

=AND(A1<"",B1A1,C1B1,D1C1)

Mike

"Daren" wrote:

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!

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 C" wrote:

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.


"Daren" wrote:

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!


"John C" wrote:

Would this work?
=AND(D2C2,C2B2,B2A2)
--
** John C **


"Daren" wrote:

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!

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 **


"Daren" wrote:

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 C" wrote:

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.


"Daren" wrote:

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!


"John C" wrote:

Would this work?
=AND(D2C2,C2B2,B2A2)
--
** John C **


"Daren" wrote:

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!

On Fri, 24 Oct 2008 12:30:01 -0700, Daren
wrote:

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!

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

Daren wrote:
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!

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))=CO LUMNS(A2:D2)

This can be expanded to any number of dates just by increasing the ranges or
inserting columns.

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 aren't dates. Check them
again.

Mike

"Daren" wrote:

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 C" wrote:

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.


"Daren" wrote:

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!


"John C" wrote:

Would this work?
=AND(D2C2,C2B2,B2A2)
--
** John C **


"Daren" wrote:

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!

Ron Rosenfeld wrote:
On Fri, 24 Oct 2008 12:30:01 -0700, Daren
wrote:

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!

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


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)}

Try using the AND function:

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

Adjust the cell references as required, and change the "<" operator to
"<=" if equal dates are to be allowed.

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
wrote:

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!

On Fri, 24 Oct 2008 15:36:42 -0500, Glenn wrote:

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)}


There are some other ways to make it "auto-expand":

=AND(SMALL(rng,ROW(INDIRECT("1:"&COLUMNS(rng))))=T RANSPOSE(rng))
--ron

Hi

I get the result as TRUE

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Daren" wrote in message
...
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!


"John C" wrote:

Would this work?
=AND(D2C2,C2B2,B2A2)
--
** John C **


"Daren" wrote:

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!

On Fri, 24 Oct 2008 19:17:27 -0400, Ron Rosenfeld
wrote:

On Fri, 24 Oct 2008 15:36:42 -0500, Glenn wrote:

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)}


There are some other ways to make it "auto-expand":

=AND(SMALL(rng,ROW(INDIRECT("1:"&COLUMNS(rng))))= TRANSPOSE(rng))
--ron


or:

=AND(SMALL(OFFSET(A1,0,0,1,COUNT(1:1)),ROW(INDIREC T("1:"&COUNT(1:1))))=TRANSPOSE(OFFSET(A1,0,0,1,COU NT(1:1))))

--ron

Might as well add my 2 cents, for a completely different approach without an
array entry

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

or expandable

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

--
Thanks,
Shane Devenshire


"Daren" wrote:

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!

A little simpler still

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

or

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

--
Thanks,
Shane Devenshire


"Daren" wrote:

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!