I'm working in Excel 2007.

I have two columns of dates, the first being a "buy" date and the second
being a "sell" date for the item in that row. If the data is valid, obviously
each date in the second column should be later than the corresponding date in
the first column.

I'd like to create a function that will compare each date in the first
column to the adjacent date in the second column, and tell me if the data is
valid or if there is a date-pair in which the "buy" date falls after the
"sell" date. I don't need this comparison returned for each pair, I just need
to check the columns as a whole and get a "VALID" OR "INVALID" value.

I'm thinking that this needs to be some sort of composite function, maybe
involving a COUNTIF(), but I just can't figure out how to work it.

Thanks for the help!

A couple of questions...

Can the sell date be the same as the buy date?

If you haven't sold yet then there probably isn't a sell date entered yet,
correct?


--
Biff
Microsoft Excel MVP


"PaladinWhite" wrote in message
...
I'm working in Excel 2007.

I have two columns of dates, the first being a "buy" date and the second
being a "sell" date for the item in that row. If the data is valid,
obviously
each date in the second column should be later than the corresponding date
in
the first column.

I'd like to create a function that will compare each date in the first
column to the adjacent date in the second column, and tell me if the data
is
valid or if there is a date-pair in which the "buy" date falls after the
"sell" date. I don't need this comparison returned for each pair, I just
need
to check the columns as a whole and get a "VALID" OR "INVALID" value.

I'm thinking that this needs to be some sort of composite function, maybe
involving a COUNTIF(), but I just can't figure out how to work it.

Thanks for the help!

No, the sell date can never be the same as the buy date - it should always be
at least a day later.

And every pair is full - there are no missing dates. I.E. every item in this
list has been sold.

"T. Valko" wrote:

A couple of questions...

Can the sell date be the same as the buy date?

If you haven't sold yet then there probably isn't a sell date entered yet,
correct?


--
Biff
Microsoft Excel MVP


"PaladinWhite" wrote in message
...
I'm working in Excel 2007.

I have two columns of dates, the first being a "buy" date and the second
being a "sell" date for the item in that row. If the data is valid,
obviously
each date in the second column should be later than the corresponding date
in
the first column.

I'd like to create a function that will compare each date in the first
column to the adjacent date in the second column, and tell me if the data
is
valid or if there is a date-pair in which the "buy" date falls after the
"sell" date. I don't need this comparison returned for each pair, I just
need
to check the columns as a whole and get a "VALID" OR "INVALID" value.

I'm thinking that this needs to be some sort of composite function, maybe
involving a COUNTIF(), but I just can't figure out how to work it.

Thanks for the help!

Try this:

=IF(SUMPRODUCT(--(A1:A10<B1:B10))=COUNT(A1:A10),"Valid","Invalid")

Note that you can't use entire columns as range references with SUMPRODUCT
unless you're using Excel 2007.

--
Biff
Microsoft Excel MVP


"PaladinWhite" wrote in message
...
No, the sell date can never be the same as the buy date - it should always
be
at least a day later.

And every pair is full - there are no missing dates. I.E. every item in
this
list has been sold.

"T. Valko" wrote:

A couple of questions...

Can the sell date be the same as the buy date?

If you haven't sold yet then there probably isn't a sell date entered
yet,
correct?


--
Biff
Microsoft Excel MVP


"PaladinWhite" wrote in message
...
I'm working in Excel 2007.

I have two columns of dates, the first being a "buy" date and the
second
being a "sell" date for the item in that row. If the data is valid,
obviously
each date in the second column should be later than the corresponding
date
in
the first column.

I'd like to create a function that will compare each date in the first
column to the adjacent date in the second column, and tell me if the
data
is
valid or if there is a date-pair in which the "buy" date falls after
the
"sell" date. I don't need this comparison returned for each pair, I
just
need
to check the columns as a whole and get a "VALID" OR "INVALID" value.

I'm thinking that this needs to be some sort of composite function,
maybe
involving a COUNTIF(), but I just can't figure out how to work it.

Thanks for the help!

Works like a charm - now I get to play around with it and try and figure out
how it works.

Thanks!

"T. Valko" wrote:

Try this:

=IF(SUMPRODUCT(--(A1:A10<B1:B10))=COUNT(A1:A10),"Valid","Invalid")

Note that you can't use entire columns as range references with SUMPRODUCT
unless you're using Excel 2007.

--
Biff
Microsoft Excel MVP


"PaladinWhite" wrote in message
...
No, the sell date can never be the same as the buy date - it should always
be
at least a day later.

And every pair is full - there are no missing dates. I.E. every item in
this
list has been sold.

"T. Valko" wrote:

A couple of questions...

Can the sell date be the same as the buy date?

If you haven't sold yet then there probably isn't a sell date entered
yet,
correct?


--
Biff
Microsoft Excel MVP


"PaladinWhite" wrote in message
...
I'm working in Excel 2007.

I have two columns of dates, the first being a "buy" date and the
second
being a "sell" date for the item in that row. If the data is valid,
obviously
each date in the second column should be later than the corresponding
date
in
the first column.

I'd like to create a function that will compare each date in the first
column to the adjacent date in the second column, and tell me if the
data
is
valid or if there is a date-pair in which the "buy" date falls after
the
"sell" date. I don't need this comparison returned for each pair, I
just
need
to check the columns as a whole and get a "VALID" OR "INVALID" value.

I'm thinking that this needs to be some sort of composite function,
maybe
involving a COUNTIF(), but I just can't figure out how to work it.

Thanks for the help!

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"PaladinWhite" wrote in message
...
Works like a charm - now I get to play around with it and try and figure
out
how it works.

Thanks!

"T. Valko" wrote:

Try this:

=IF(SUMPRODUCT(--(A1:A10<B1:B10))=COUNT(A1:A10),"Valid","Invalid")

Note that you can't use entire columns as range references with
SUMPRODUCT
unless you're using Excel 2007.

--
Biff
Microsoft Excel MVP


"PaladinWhite" wrote in message
...
No, the sell date can never be the same as the buy date - it should
always
be
at least a day later.

And every pair is full - there are no missing dates. I.E. every item in
this
list has been sold.

"T. Valko" wrote:

A couple of questions...

Can the sell date be the same as the buy date?

If you haven't sold yet then there probably isn't a sell date entered
yet,
correct?


--
Biff
Microsoft Excel MVP


"PaladinWhite" wrote in
message
...
I'm working in Excel 2007.

I have two columns of dates, the first being a "buy" date and the
second
being a "sell" date for the item in that row. If the data is valid,
obviously
each date in the second column should be later than the
corresponding
date
in
the first column.

I'd like to create a function that will compare each date in the
first
column to the adjacent date in the second column, and tell me if the
data
is
valid or if there is a date-pair in which the "buy" date falls after
the
"sell" date. I don't need this comparison returned for each pair, I
just
need
to check the columns as a whole and get a "VALID" OR "INVALID"
value.

I'm thinking that this needs to be some sort of composite function,
maybe
involving a COUNTIF(), but I just can't figure out how to work it.

Thanks for the help!