Date comparison
 

For the data below B1A1 is true.

A B
12/3/07 11:48 AM 12/3/07 1:26 PM

How do I check the dates are at least 1 day apart, or 24 hours apart?
I am looking for a check to inlcude in a sumproduct, using external sheet
reference, like:

SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001))

where C and D contain Scheduled date and Shipped date.

Also I am trying to avoid reformatting the source data or adding
suplementary, intermediate columns.

Thank you.

Date comparison
 

You can subtract the dates. For example =IF(B1-A1=1,"Dates are at least 1
day apart","Dates are the same day"). This formula assumes there are valid
dates in A1 and B1 as it does no checking. Excel maintains dates and times
as numbers. The integral part of the number is the day, the fractional part
the time. For example Feb. 1, 2008 12:00 PM, noon, is 39479.5 Jan. 1, 1900
is day 1. So Feb 1, 2008 is day 39,479. The .5 represents 12/24. 12:00 AM,
midnight, is 0.0 3:00 AM is 3/24 = 0.125 3:00 PM is 15/24 = 0.625 To see
the numbers, press Ctrl+Accent Grave (top row, leftmost key). Press
Ctrl+Accent Grave to return to normal display.

Tyro

"adimar" wrote in message
...

For the data below B1A1 is true.

A B
12/3/07 11:48 AM 12/3/07 1:26 PM

How do I check the dates are at least 1 day apart, or 24 hours apart?
I am looking for a check to inlcude in a sumproduct, using external sheet
reference, like:

SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001))

where C and D contain Scheduled date and Shipped date.

Also I am trying to avoid reformatting the source data or adding
suplementary, intermediate columns.

Thank you.

Date comparison
 

Thank you for the explanation. Yes, this makes sense and works for a quick
test I did.

But I have one example that I don't know how to fix.
Since this one works fine:
RawData!$AF$1:$AF$10000RawData!$AD$1:$AD$10000

Why does this return a #VALUE error?
RawData!$AF$1:$AF$10000-1RawData!$AD$1:$AD$10000

Thank you.


"Tyro" wrote:

You can subtract the dates. For example =IF(B1-A1=1,"Dates are at least 1
day apart","Dates are the same day"). This formula assumes there are valid
dates in A1 and B1 as it does no checking. Excel maintains dates and times
as numbers. The integral part of the number is the day, the fractional part
the time. For example Feb. 1, 2008 12:00 PM, noon, is 39479.5 Jan. 1, 1900
is day 1. So Feb 1, 2008 is day 39,479. The .5 represents 12/24. 12:00 AM,
midnight, is 0.0 3:00 AM is 3/24 = 0.125 3:00 PM is 15/24 = 0.625 To see
the numbers, press Ctrl+Accent Grave (top row, leftmost key). Press
Ctrl+Accent Grave to return to normal display.

Tyro

"adimar" wrote in message
...

For the data below B1A1 is true.

A B
12/3/07 11:48 AM 12/3/07 1:26 PM

How do I check the dates are at least 1 day apart, or 24 hours apart?
I am looking for a check to inlcude in a sumproduct, using external sheet
reference, like:

SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001))

where C and D contain Scheduled date and Shipped date.

Also I am trying to avoid reformatting the source data or adding
suplementary, intermediate columns.

Thank you.

Date comparison
 

What is the whole formula?

Tyro

"adimar" wrote in message
...

Thank you for the explanation. Yes, this makes sense and works for a quick
test I did.

But I have one example that I don't know how to fix.
Since this one works fine:
RawData!$AF$1:$AF$10000RawData!$AD$1:$AD$10000

Why does this return a #VALUE error?
RawData!$AF$1:$AF$10000-1RawData!$AD$1:$AD$10000

Thank you.


"Tyro" wrote:

You can subtract the dates. For example =IF(B1-A1=1,"Dates are at least
1
day apart","Dates are the same day"). This formula assumes there are
valid
dates in A1 and B1 as it does no checking. Excel maintains dates and
times
as numbers. The integral part of the number is the day, the fractional
part
the time. For example Feb. 1, 2008 12:00 PM, noon, is 39479.5 Jan. 1,
1900
is day 1. So Feb 1, 2008 is day 39,479. The .5 represents 12/24. 12:00
AM,
midnight, is 0.0 3:00 AM is 3/24 = 0.125 3:00 PM is 15/24 = 0.625 To
see
the numbers, press Ctrl+Accent Grave (top row, leftmost key). Press
Ctrl+Accent Grave to return to normal display.

Tyro

"adimar" wrote in message
...

For the data below B1A1 is true.

A B
12/3/07 11:48 AM 12/3/07 1:26 PM

How do I check the dates are at least 1 day apart, or 24 hours apart?
I am looking for a check to inlcude in a sumproduct, using external
sheet
reference, like:

SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001))

where C and D contain Scheduled date and Shipped date.

Also I am trying to avoid reformatting the source data or adding
suplementary, intermediate columns.

Thank you.

Date comparison
 

This one works:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000RawData!$AD$1:$AD$10000)*((ISNUMBER( MATCH(RawData!$E$1:$E$10000,Definitions!$B$3:$B$6, 0)))))

This one returns #VALUE error, see next to last multiplier:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000-1RawData!$AD$1:$AD$10000)*((ISNUMBER(MATCH(RawDat a!$E$1:$E$10000,Definitions!$B$3:$B$6,0)))))


Thank you.

"Tyro" wrote:

What is the whole formula?

Tyro

"adimar" wrote in message
...

Thank you for the explanation. Yes, this makes sense and works for a quick
test I did.

But I have one example that I don't know how to fix.
Since this one works fine:
RawData!$AF$1:$AF$10000RawData!$AD$1:$AD$10000

Why does this return a #VALUE error?
RawData!$AF$1:$AF$10000-1RawData!$AD$1:$AD$10000

Thank you.


"Tyro" wrote:

You can subtract the dates. For example =IF(B1-A1=1,"Dates are at least
1
day apart","Dates are the same day"). This formula assumes there are
valid
dates in A1 and B1 as it does no checking. Excel maintains dates and
times
as numbers. The integral part of the number is the day, the fractional
part
the time. For example Feb. 1, 2008 12:00 PM, noon, is 39479.5 Jan. 1,
1900
is day 1. So Feb 1, 2008 is day 39,479. The .5 represents 12/24. 12:00
AM,
midnight, is 0.0 3:00 AM is 3/24 = 0.125 3:00 PM is 15/24 = 0.625 To
see
the numbers, press Ctrl+Accent Grave (top row, leftmost key). Press
Ctrl+Accent Grave to return to normal display.

Tyro

"adimar" wrote in message
...

For the data below B1A1 is true.

A B
12/3/07 11:48 AM 12/3/07 1:26 PM

How do I check the dates are at least 1 day apart, or 24 hours apart?
I am looking for a check to inlcude in a sumproduct, using external
sheet
reference, like:

SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001))

where C and D contain Scheduled date and Shipped date.

Also I am trying to avoid reformatting the source data or adding
suplementary, intermediate columns.

Thank you.

Date comparison
 

Looks like in $AF$1:$AF$10000 you have non-numerics so that when the "-1" is
attempted, Excel sees a #value error. Do a =COUNT($AF$1:$AF$10000) to see
how many numeric cells there are and do a
=SUMPRODUCT(--($AF$1:$AF$10000<"") to see how many non-blank cells there
are. If you have only numbers in that range, both functions should produce
the same number. If they don't you have some non-numerics in your range.

Tyro


"adimar" wrote in message
...

This one works:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000RawData!$AD$1:$AD$10000)*((ISNUMBER( MATCH(RawData!$E$1:$E$10000,Definitions!$B$3:$B$6, 0)))))

This one returns #VALUE error, see next to last multiplier:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000-1RawData!$AD$1:$AD$10000)*((ISNUMBER(MATCH(RawDat a!$E$1:$E$10000,Definitions!$B$3:$B$6,0)))))


Thank you.

"Tyro" wrote:

What is the whole formula?

Tyro

"adimar" wrote in message
...

Thank you for the explanation. Yes, this makes sense and works for a
quick
test I did.

But I have one example that I don't know how to fix.
Since this one works fine:
RawData!$AF$1:$AF$10000RawData!$AD$1:$AD$10000

Why does this return a #VALUE error?
RawData!$AF$1:$AF$10000-1RawData!$AD$1:$AD$10000

Thank you.


"Tyro" wrote:

You can subtract the dates. For example =IF(B1-A1=1,"Dates are at
least
1
day apart","Dates are the same day"). This formula assumes there are
valid
dates in A1 and B1 as it does no checking. Excel maintains dates and
times
as numbers. The integral part of the number is the day, the fractional
part
the time. For example Feb. 1, 2008 12:00 PM, noon, is 39479.5 Jan. 1,
1900
is day 1. So Feb 1, 2008 is day 39,479. The .5 represents 12/24. 12:00
AM,
midnight, is 0.0 3:00 AM is 3/24 = 0.125 3:00 PM is 15/24 = 0.625 To
see
the numbers, press Ctrl+Accent Grave (top row, leftmost key). Press
Ctrl+Accent Grave to return to normal display.

Tyro

"adimar" wrote in message
...

For the data below B1A1 is true.

A B
12/3/07 11:48 AM 12/3/07 1:26 PM

How do I check the dates are at least 1 day apart, or 24 hours
apart?
I am looking for a check to inlcude in a sumproduct, using external
sheet
reference, like:

SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001))

where C and D contain Scheduled date and Shipped date.

Also I am trying to avoid reformatting the source data or adding
suplementary, intermediate columns.

Thank you.

Date comparison
 

=SUMPRODUCT(--($AF$1:$AF$10000<"") should be
=SUMPRODUCT(--($AF$1:$AF$10000<"")) or even
=COUNTIF($AF$1:$AF$10000,"<"&"")

Tyro

"Tyro" wrote in message
t...
Looks like in $AF$1:$AF$10000 you have non-numerics so that when the "-1"
is attempted, Excel sees a #value error. Do a =COUNT($AF$1:$AF$10000) to
see how many numeric cells there are and do a
=SUMPRODUCT(--($AF$1:$AF$10000<"") to see how many non-blank cells there
are. If you have only numbers in that range, both functions should produce
the same number. If they don't you have some non-numerics in your range.

Tyro


"adimar" wrote in message
...

This one works:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000RawData!$AD$1:$AD$10000)*((ISNUMBER( MATCH(RawData!$E$1:$E$10000,Definitions!$B$3:$B$6, 0)))))

This one returns #VALUE error, see next to last multiplier:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000-1RawData!$AD$1:$AD$10000)*((ISNUMBER(MATCH(RawDat a!$E$1:$E$10000,Definitions!$B$3:$B$6,0)))))


Thank you.

"Tyro" wrote:

What is the whole formula?

Tyro

"adimar" wrote in message
...

Thank you for the explanation. Yes, this makes sense and works for a
quick
test I did.

But I have one example that I don't know how to fix.
Since this one works fine:
RawData!$AF$1:$AF$10000RawData!$AD$1:$AD$10000

Why does this return a #VALUE error?
RawData!$AF$1:$AF$10000-1RawData!$AD$1:$AD$10000

Thank you.


"Tyro" wrote:

You can subtract the dates. For example =IF(B1-A1=1,"Dates are at
least
1
day apart","Dates are the same day"). This formula assumes there are
valid
dates in A1 and B1 as it does no checking. Excel maintains dates and
times
as numbers. The integral part of the number is the day, the
fractional
part
the time. For example Feb. 1, 2008 12:00 PM, noon, is 39479.5 Jan.
1,
1900
is day 1. So Feb 1, 2008 is day 39,479. The .5 represents 12/24.
12:00
AM,
midnight, is 0.0 3:00 AM is 3/24 = 0.125 3:00 PM is 15/24 = 0.625
To
see
the numbers, press Ctrl+Accent Grave (top row, leftmost key). Press
Ctrl+Accent Grave to return to normal display.

Tyro

"adimar" wrote in message
...

For the data below B1A1 is true.

A B
12/3/07 11:48 AM 12/3/07 1:26 PM

How do I check the dates are at least 1 day apart, or 24 hours
apart?
I am looking for a check to inlcude in a sumproduct, using external
sheet
reference, like:

SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001))

where C and D contain Scheduled date and Shipped date.

Also I am trying to avoid reformatting the source data or adding
suplementary, intermediate columns.

Thank you.

Date comparison
 

Yes, that's correct - I have some 4600 records in RawData (equal to number
of ADs) and some 3000 non-blank AFs.

How then can I change the formula to allow for blank date fields in AF. ADs
should always be non-blank but if there's a way to check for that, that would
be great.


Thank you.

"Tyro" wrote:

=SUMPRODUCT(--($AF$1:$AF$10000<"") should be
=SUMPRODUCT(--($AF$1:$AF$10000<"")) or even
=COUNTIF($AF$1:$AF$10000,"<"&"")

Tyro

"Tyro" wrote in message
t...
Looks like in $AF$1:$AF$10000 you have non-numerics so that when the "-1"
is attempted, Excel sees a #value error. Do a =COUNT($AF$1:$AF$10000) to
see how many numeric cells there are and do a
=SUMPRODUCT(--($AF$1:$AF$10000<"") to see how many non-blank cells there
are. If you have only numbers in that range, both functions should produce
the same number. If they don't you have some non-numerics in your range.

Tyro


"adimar" wrote in message
...

This one works:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000RawData!$AD$1:$AD$10000)*((ISNUMBER( MATCH(RawData!$E$1:$E$10000,Definitions!$B$3:$B$6, 0)))))

This one returns #VALUE error, see next to last multiplier:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000-1RawData!$AD$1:$AD$10000)*((ISNUMBER(MATCH(RawDat a!$E$1:$E$10000,Definitions!$B$3:$B$6,0)))))


Thank you.

"Tyro" wrote:

What is the whole formula?

Tyro

"adimar" wrote in message
...

Thank you for the explanation. Yes, this makes sense and works for a
quick
test I did.

But I have one example that I don't know how to fix.
Since this one works fine:
RawData!$AF$1:$AF$10000RawData!$AD$1:$AD$10000

Why does this return a #VALUE error?
RawData!$AF$1:$AF$10000-1RawData!$AD$1:$AD$10000

Thank you.


"Tyro" wrote:

You can subtract the dates. For example =IF(B1-A1=1,"Dates are at
least
1
day apart","Dates are the same day"). This formula assumes there are
valid
dates in A1 and B1 as it does no checking. Excel maintains dates and
times
as numbers. The integral part of the number is the day, the
fractional
part
the time. For example Feb. 1, 2008 12:00 PM, noon, is 39479.5 Jan.
1,
1900
is day 1. So Feb 1, 2008 is day 39,479. The .5 represents 12/24.
12:00
AM,
midnight, is 0.0 3:00 AM is 3/24 = 0.125 3:00 PM is 15/24 = 0.625
To
see
the numbers, press Ctrl+Accent Grave (top row, leftmost key). Press
Ctrl+Accent Grave to return to normal display.

Tyro

"adimar" wrote in message
...

For the data below B1A1 is true.

A B
12/3/07 11:48 AM 12/3/07 1:26 PM

How do I check the dates are at least 1 day apart, or 24 hours
apart?
I am looking for a check to inlcude in a sumproduct, using external
sheet
reference, like:

SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001))

where C and D contain Scheduled date and Shipped date.

Also I am trying to avoid reformatting the source data or adding
suplementary, intermediate columns.

Thank you.

Date comparison
 

Your problem is elsewhere. If a cell is blank and you subtract 1 from it,
you get -1. If a cell contains text, and you subtract -1 from it you get
#VALUE
Do you have some cells containing text in your range?

Tyro

"adimar" wrote in message
...

Yes, that's correct - I have some 4600 records in RawData (equal to number
of ADs) and some 3000 non-blank AFs.

How then can I change the formula to allow for blank date fields in AF.
ADs
should always be non-blank but if there's a way to check for that, that
would
be great.


Thank you.

"Tyro" wrote:

=SUMPRODUCT(--($AF$1:$AF$10000<"") should be
=SUMPRODUCT(--($AF$1:$AF$10000<"")) or even
=COUNTIF($AF$1:$AF$10000,"<"&"")

Tyro

"Tyro" wrote in message
t...
Looks like in $AF$1:$AF$10000 you have non-numerics so that when the
"-1"
is attempted, Excel sees a #value error. Do a =COUNT($AF$1:$AF$10000)
to
see how many numeric cells there are and do a
=SUMPRODUCT(--($AF$1:$AF$10000<"") to see how many non-blank cells
there
are. If you have only numbers in that range, both functions should
produce
the same number. If they don't you have some non-numerics in your
range.

Tyro


"adimar" wrote in message
...

This one works:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000RawData!$AD$1:$AD$10000)*((ISNUMBER( MATCH(RawData!$E$1:$E$10000,Definitions!$B$3:$B$6, 0)))))

This one returns #VALUE error, see next to last multiplier:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000-1RawData!$AD$1:$AD$10000)*((ISNUMBER(MATCH(RawDat a!$E$1:$E$10000,Definitions!$B$3:$B$6,0)))))


Thank you.

"Tyro" wrote:

What is the whole formula?

Tyro

"adimar" wrote in message
...

Thank you for the explanation. Yes, this makes sense and works for
a
quick
test I did.

But I have one example that I don't know how to fix.
Since this one works fine:
RawData!$AF$1:$AF$10000RawData!$AD$1:$AD$10000

Why does this return a #VALUE error?
RawData!$AF$1:$AF$10000-1RawData!$AD$1:$AD$10000

Thank you.


"Tyro" wrote:

You can subtract the dates. For example =IF(B1-A1=1,"Dates are at
least
1
day apart","Dates are the same day"). This formula assumes there
are
valid
dates in A1 and B1 as it does no checking. Excel maintains dates
and
times
as numbers. The integral part of the number is the day, the
fractional
part
the time. For example Feb. 1, 2008 12:00 PM, noon, is 39479.5
Jan.
1,
1900
is day 1. So Feb 1, 2008 is day 39,479. The .5 represents 12/24.
12:00
AM,
midnight, is 0.0 3:00 AM is 3/24 = 0.125 3:00 PM is 15/24 =
0.625
To
see
the numbers, press Ctrl+Accent Grave (top row, leftmost key).
Press
Ctrl+Accent Grave to return to normal display.

Tyro

"adimar" wrote in message
...

For the data below B1A1 is true.

A B
12/3/07 11:48 AM 12/3/07 1:26 PM

How do I check the dates are at least 1 day apart, or 24 hours
apart?
I am looking for a check to inlcude in a sumproduct, using
external
sheet
reference, like:

SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001))

where C and D contain Scheduled date and Shipped date.

Also I am trying to avoid reformatting the source data or adding
suplementary, intermediate columns.

Thank you.

Date comparison
 

Or if you have a formula that returns the empty string, which looks like a
blank, and you subtract 1 from that, you'll also get #VALUE

Tyro

"Tyro" wrote in message
t...
Your problem is elsewhere. If a cell is blank and you subtract 1 from it,
you get -1. If a cell contains text, and you subtract -1 from it you get
#VALUE
Do you have some cells containing text in your range?

Tyro

"adimar" wrote in message
...

Yes, that's correct - I have some 4600 records in RawData (equal to
number
of ADs) and some 3000 non-blank AFs.

How then can I change the formula to allow for blank date fields in AF.
ADs
should always be non-blank but if there's a way to check for that, that
would
be great.


Thank you.

"Tyro" wrote:

=SUMPRODUCT(--($AF$1:$AF$10000<"") should be
=SUMPRODUCT(--($AF$1:$AF$10000<"")) or even
=COUNTIF($AF$1:$AF$10000,"<"&"")

Tyro

"Tyro" wrote in message
t...
Looks like in $AF$1:$AF$10000 you have non-numerics so that when the
"-1"
is attempted, Excel sees a #value error. Do a =COUNT($AF$1:$AF$10000)
to
see how many numeric cells there are and do a
=SUMPRODUCT(--($AF$1:$AF$10000<"") to see how many non-blank cells
there
are. If you have only numbers in that range, both functions should
produce
the same number. If they don't you have some non-numerics in your
range.

Tyro


"adimar" wrote in message
...

This one works:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000RawData!$AD$1:$AD$10000)*((ISNUMBER( MATCH(RawData!$E$1:$E$10000,Definitions!$B$3:$B$6, 0)))))

This one returns #VALUE error, see next to last multiplier:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000-1RawData!$AD$1:$AD$10000)*((ISNUMBER(MATCH(RawDat a!$E$1:$E$10000,Definitions!$B$3:$B$6,0)))))


Thank you.

"Tyro" wrote:

What is the whole formula?

Tyro

"adimar" wrote in message
...

Thank you for the explanation. Yes, this makes sense and works for
a
quick
test I did.

But I have one example that I don't know how to fix.
Since this one works fine:
RawData!$AF$1:$AF$10000RawData!$AD$1:$AD$10000

Why does this return a #VALUE error?
RawData!$AF$1:$AF$10000-1RawData!$AD$1:$AD$10000

Thank you.


"Tyro" wrote:

You can subtract the dates. For example =IF(B1-A1=1,"Dates are
at
least
1
day apart","Dates are the same day"). This formula assumes there
are
valid
dates in A1 and B1 as it does no checking. Excel maintains dates
and
times
as numbers. The integral part of the number is the day, the
fractional
part
the time. For example Feb. 1, 2008 12:00 PM, noon, is 39479.5
Jan.
1,
1900
is day 1. So Feb 1, 2008 is day 39,479. The .5 represents 12/24.
12:00
AM,
midnight, is 0.0 3:00 AM is 3/24 = 0.125 3:00 PM is 15/24 =
0.625
To
see
the numbers, press Ctrl+Accent Grave (top row, leftmost key).
Press
Ctrl+Accent Grave to return to normal display.

Tyro

"adimar" wrote in message
...

For the data below B1A1 is true.

A B
12/3/07 11:48 AM 12/3/07 1:26 PM

How do I check the dates are at least 1 day apart, or 24 hours
apart?
I am looking for a check to inlcude in a sumproduct, using
external
sheet
reference, like:

SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001))

where C and D contain Scheduled date and Shipped date.

Also I am trying to avoid reformatting the source data or
adding
suplementary, intermediate columns.

Thank you.

Date comparison
 

The columns I'm comparing (AD and AF) are dates generated by an Oracle report.
I assume they are "", when not a regular/valid date.

I have these types of situations:

AD AF
9/30/2007 12/21/2007
10/18/2007

That is, AF could be "" but all ADs are valid dates.
I need to count how many records have AFAD, when AF<"".

Thank you.
================

"Tyro" wrote:

Or if you have a formula that returns the empty string, which looks like a
blank, and you subtract 1 from that, you'll also get #VALUE

Tyro

"Tyro" wrote in message
t...
Your problem is elsewhere. If a cell is blank and you subtract 1 from it,
you get -1. If a cell contains text, and you subtract -1 from it you get
#VALUE
Do you have some cells containing text in your range?

Tyro

"adimar" wrote in message
...

Yes, that's correct - I have some 4600 records in RawData (equal to
number
of ADs) and some 3000 non-blank AFs.

How then can I change the formula to allow for blank date fields in AF.
ADs
should always be non-blank but if there's a way to check for that, that
would
be great.


Thank you.

"Tyro" wrote:

=SUMPRODUCT(--($AF$1:$AF$10000<"") should be
=SUMPRODUCT(--($AF$1:$AF$10000<"")) or even
=COUNTIF($AF$1:$AF$10000,"<"&"")

Tyro

"Tyro" wrote in message
t...
Looks like in $AF$1:$AF$10000 you have non-numerics so that when the
"-1"
is attempted, Excel sees a #value error. Do a =COUNT($AF$1:$AF$10000)
to
see how many numeric cells there are and do a
=SUMPRODUCT(--($AF$1:$AF$10000<"") to see how many non-blank cells
there
are. If you have only numbers in that range, both functions should
produce
the same number. If they don't you have some non-numerics in your
range.

Tyro


"adimar" wrote in message
...

This one works:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000RawData!$AD$1:$AD$10000)*((ISNUMBER( MATCH(RawData!$E$1:$E$10000,Definitions!$B$3:$B$6, 0)))))

This one returns #VALUE error, see next to last multiplier:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000-1RawData!$AD$1:$AD$10000)*((ISNUMBER(MATCH(RawDat a!$E$1:$E$10000,Definitions!$B$3:$B$6,0)))))


Thank you.

"Tyro" wrote:

What is the whole formula?

Tyro

"adimar" wrote in message
...

Thank you for the explanation. Yes, this makes sense and works for
a
quick
test I did.

But I have one example that I don't know how to fix.
Since this one works fine:
RawData!$AF$1:$AF$10000RawData!$AD$1:$AD$10000

Why does this return a #VALUE error?
RawData!$AF$1:$AF$10000-1RawData!$AD$1:$AD$10000

Thank you.


"Tyro" wrote:

You can subtract the dates. For example =IF(B1-A1=1,"Dates are
at
least
1
day apart","Dates are the same day"). This formula assumes there
are
valid
dates in A1 and B1 as it does no checking. Excel maintains dates
and
times
as numbers. The integral part of the number is the day, the
fractional
part
the time. For example Feb. 1, 2008 12:00 PM, noon, is 39479.5
Jan.
1,
1900
is day 1. So Feb 1, 2008 is day 39,479. The .5 represents 12/24.
12:00
AM,
midnight, is 0.0 3:00 AM is 3/24 = 0.125 3:00 PM is 15/24 =
0.625
To
see
the numbers, press Ctrl+Accent Grave (top row, leftmost key).
Press
Ctrl+Accent Grave to return to normal display.

Tyro

"adimar" wrote in message
...

For the data below B1A1 is true.

A B
12/3/07 11:48 AM 12/3/07 1:26 PM

How do I check the dates are at least 1 day apart, or 24 hours
apart?
I am looking for a check to inlcude in a sumproduct, using
external
sheet
reference, like:

SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001))

where C and D contain Scheduled date and Shipped date.

Also I am trying to avoid reformatting the source data or
adding
suplementary, intermediate columns.

Thank you.

Date comparison
 

This one seems to work:
((NOT(ISERROR((RawData!$AF$1:$AF$10000-1RawData!$AD$1:$AD$10000)))

Is this correct?


"Tyro" wrote:

Or if you have a formula that returns the empty string, which looks like a
blank, and you subtract 1 from that, you'll also get #VALUE

Tyro

"Tyro" wrote in message
t...
Your problem is elsewhere. If a cell is blank and you subtract 1 from it,
you get -1. If a cell contains text, and you subtract -1 from it you get
#VALUE
Do you have some cells containing text in your range?

Tyro

"adimar" wrote in message
...

Yes, that's correct - I have some 4600 records in RawData (equal to
number
of ADs) and some 3000 non-blank AFs.

How then can I change the formula to allow for blank date fields in AF.
ADs
should always be non-blank but if there's a way to check for that, that
would
be great.


Thank you.

"Tyro" wrote:

=SUMPRODUCT(--($AF$1:$AF$10000<"") should be
=SUMPRODUCT(--($AF$1:$AF$10000<"")) or even
=COUNTIF($AF$1:$AF$10000,"<"&"")

Tyro

"Tyro" wrote in message
t...
Looks like in $AF$1:$AF$10000 you have non-numerics so that when the
"-1"
is attempted, Excel sees a #value error. Do a =COUNT($AF$1:$AF$10000)
to
see how many numeric cells there are and do a
=SUMPRODUCT(--($AF$1:$AF$10000<"") to see how many non-blank cells
there
are. If you have only numbers in that range, both functions should
produce
the same number. If they don't you have some non-numerics in your
range.

Tyro


"adimar" wrote in message
...

This one works:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000RawData!$AD$1:$AD$10000)*((ISNUMBER( MATCH(RawData!$E$1:$E$10000,Definitions!$B$3:$B$6, 0)))))

This one returns #VALUE error, see next to last multiplier:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000-1RawData!$AD$1:$AD$10000)*((ISNUMBER(MATCH(RawDat a!$E$1:$E$10000,Definitions!$B$3:$B$6,0)))))


Thank you.

"Tyro" wrote:

What is the whole formula?

Tyro

"adimar" wrote in message
...

Thank you for the explanation. Yes, this makes sense and works for
a
quick
test I did.

But I have one example that I don't know how to fix.
Since this one works fine:
RawData!$AF$1:$AF$10000RawData!$AD$1:$AD$10000

Why does this return a #VALUE error?
RawData!$AF$1:$AF$10000-1RawData!$AD$1:$AD$10000

Thank you.


"Tyro" wrote:

You can subtract the dates. For example =IF(B1-A1=1,"Dates are
at
least
1
day apart","Dates are the same day"). This formula assumes there
are
valid
dates in A1 and B1 as it does no checking. Excel maintains dates
and
times
as numbers. The integral part of the number is the day, the
fractional
part
the time. For example Feb. 1, 2008 12:00 PM, noon, is 39479.5
Jan.
1,
1900
is day 1. So Feb 1, 2008 is day 39,479. The .5 represents 12/24.
12:00
AM,
midnight, is 0.0 3:00 AM is 3/24 = 0.125 3:00 PM is 15/24 =
0.625
To
see
the numbers, press Ctrl+Accent Grave (top row, leftmost key).
Press
Ctrl+Accent Grave to return to normal display.

Tyro

"adimar" wrote in message
...

For the data below B1A1 is true.

A B
12/3/07 11:48 AM 12/3/07 1:26 PM

How do I check the dates are at least 1 day apart, or 24 hours
apart?
I am looking for a check to inlcude in a sumproduct, using
external
sheet
reference, like:

SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001))

where C and D contain Scheduled date and Shipped date.

Also I am trying to avoid reformatting the source data or
adding
suplementary, intermediate columns.

Thank you.

Date comparison
 

Assuming your dates occupy the first 100 rows, cells in AF1:AF100 that are
truly empty will not be greater than a date. However, cells with the empty
string will be greater than a date. So use this formula

=SUMPRODUCT(--(AF1:AF100AD1:AD100))-COUNTIF(AF1:AF100,"="&"").

Tyro


"adimar" wrote in message
...
The columns I'm comparing (AD and AF) are dates generated by an Oracle
report.
I assume they are "", when not a regular/valid date.

I have these types of situations:

AD AF
9/30/2007 12/21/2007
10/18/2007

That is, AF could be "" but all ADs are valid dates.
I need to count how many records have AFAD, when AF<"".

Thank you.
================

"Tyro" wrote:

Or if you have a formula that returns the empty string, which looks like
a
blank, and you subtract 1 from that, you'll also get #VALUE

Tyro

"Tyro" wrote in message
t...
Your problem is elsewhere. If a cell is blank and you subtract 1 from
it,
you get -1. If a cell contains text, and you subtract -1 from it you
get
#VALUE
Do you have some cells containing text in your range?

Tyro

"adimar" wrote in message
...

Yes, that's correct - I have some 4600 records in RawData (equal to
number
of ADs) and some 3000 non-blank AFs.

How then can I change the formula to allow for blank date fields in
AF.
ADs
should always be non-blank but if there's a way to check for that,
that
would
be great.


Thank you.

"Tyro" wrote:

=SUMPRODUCT(--($AF$1:$AF$10000<"") should be
=SUMPRODUCT(--($AF$1:$AF$10000<"")) or even
=COUNTIF($AF$1:$AF$10000,"<"&"")

Tyro

"Tyro" wrote in message
t...
Looks like in $AF$1:$AF$10000 you have non-numerics so that when
the
"-1"
is attempted, Excel sees a #value error. Do a
=COUNT($AF$1:$AF$10000)
to
see how many numeric cells there are and do a
=SUMPRODUCT(--($AF$1:$AF$10000<"") to see how many non-blank cells
there
are. If you have only numbers in that range, both functions should
produce
the same number. If they don't you have some non-numerics in your
range.

Tyro


"adimar" wrote in message
...

This one works:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000RawData!$AD$1:$AD$10000)*((ISNUMBER( MATCH(RawData!$E$1:$E$10000,Definitions!$B$3:$B$6, 0)))))

This one returns #VALUE error, see next to last multiplier:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000-1RawData!$AD$1:$AD$10000)*((ISNUMBER(MATCH(RawDat a!$E$1:$E$10000,Definitions!$B$3:$B$6,0)))))


Thank you.

"Tyro" wrote:

What is the whole formula?

Tyro

"adimar" wrote in message
...

Thank you for the explanation. Yes, this makes sense and works
for
a
quick
test I did.

But I have one example that I don't know how to fix.
Since this one works fine:
RawData!$AF$1:$AF$10000RawData!$AD$1:$AD$10000

Why does this return a #VALUE error?
RawData!$AF$1:$AF$10000-1RawData!$AD$1:$AD$10000

Thank you.


"Tyro" wrote:

You can subtract the dates. For example =IF(B1-A1=1,"Dates
are
at
least
1
day apart","Dates are the same day"). This formula assumes
there
are
valid
dates in A1 and B1 as it does no checking. Excel maintains
dates
and
times
as numbers. The integral part of the number is the day, the
fractional
part
the time. For example Feb. 1, 2008 12:00 PM, noon, is 39479.5
Jan.
1,
1900
is day 1. So Feb 1, 2008 is day 39,479. The .5 represents
12/24.
12:00
AM,
midnight, is 0.0 3:00 AM is 3/24 = 0.125 3:00 PM is 15/24 =
0.625
To
see
the numbers, press Ctrl+Accent Grave (top row, leftmost key).
Press
Ctrl+Accent Grave to return to normal display.

Tyro

"adimar" wrote in message
...

For the data below B1A1 is true.

A B
12/3/07 11:48 AM 12/3/07 1:26 PM

How do I check the dates are at least 1 day apart, or 24
hours
apart?
I am looking for a check to inlcude in a sumproduct, using
external
sheet
reference, like:

SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001))

where C and D contain Scheduled date and Shipped date.

Also I am trying to avoid reformatting the source data or
adding
suplementary, intermediate columns.

Thank you.

Date comparison
 

(For some reason my lasy post didnt post€¦ let me try again.)

This only works if all ADs are valid dates.
How do I specify a dynamic range, so instead of the 10000 max records in
this formula ($AF$1:$AF$1000$AD$1:$AD$1000)
I use something like indirect for the range. I tried several formulas and
cannot et over syntax errors.

Thank you.
==========

"Tyro" wrote:

Assuming your dates occupy the first 100 rows, cells in AF1:AF100 that are
truly empty will not be greater than a date. However, cells with the empty
string will be greater than a date. So use this formula

=SUMPRODUCT(--(AF1:AF100AD1:AD100))-COUNTIF(AF1:AF100,"="&"").

Tyro


"adimar" wrote in message
...
The columns I'm comparing (AD and AF) are dates generated by an Oracle
report.
I assume they are "", when not a regular/valid date.

I have these types of situations:

AD AF
9/30/2007 12/21/2007
10/18/2007

That is, AF could be "" but all ADs are valid dates.
I need to count how many records have AFAD, when AF<"".

Thank you.
================

"Tyro" wrote:

Or if you have a formula that returns the empty string, which looks like
a
blank, and you subtract 1 from that, you'll also get #VALUE

Tyro

"Tyro" wrote in message
t...
Your problem is elsewhere. If a cell is blank and you subtract 1 from
it,
you get -1. If a cell contains text, and you subtract -1 from it you
get
#VALUE
Do you have some cells containing text in your range?

Tyro

"adimar" wrote in message
...

Yes, that's correct - I have some 4600 records in RawData (equal to
number
of ADs) and some 3000 non-blank AFs.

How then can I change the formula to allow for blank date fields in
AF.
ADs
should always be non-blank but if there's a way to check for that,
that
would
be great.


Thank you.

"Tyro" wrote:

=SUMPRODUCT(--($AF$1:$AF$10000<"") should be
=SUMPRODUCT(--($AF$1:$AF$10000<"")) or even
=COUNTIF($AF$1:$AF$10000,"<"&"")

Tyro

"Tyro" wrote in message
t...
Looks like in $AF$1:$AF$10000 you have non-numerics so that when
the
"-1"
is attempted, Excel sees a #value error. Do a
=COUNT($AF$1:$AF$10000)
to
see how many numeric cells there are and do a
=SUMPRODUCT(--($AF$1:$AF$10000<"") to see how many non-blank cells
there
are. If you have only numbers in that range, both functions should
produce
the same number. If they don't you have some non-numerics in your
range.

Tyro


"adimar" wrote in message
...

This one works:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000RawData!$AD$1:$AD$10000)*((ISNUMBER( MATCH(RawData!$E$1:$E$10000,Definitions!$B$3:$B$6, 0)))))

This one returns #VALUE error, see next to last multiplier:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000-1RawData!$AD$1:$AD$10000)*((ISNUMBER(MATCH(RawDat a!$E$1:$E$10000,Definitions!$B$3:$B$6,0)))))


Thank you.

"Tyro" wrote:

What is the whole formula?

Tyro

"adimar" wrote in message
...

Thank you for the explanation. Yes, this makes sense and works
for
a
quick
test I did.

But I have one example that I don't know how to fix.
Since this one works fine:
RawData!$AF$1:$AF$10000RawData!$AD$1:$AD$10000

Why does this return a #VALUE error?
RawData!$AF$1:$AF$10000-1RawData!$AD$1:$AD$10000

Thank you.


"Tyro" wrote:

You can subtract the dates. For example =IF(B1-A1=1,"Dates
are
at
least
1
day apart","Dates are the same day"). This formula assumes
there
are
valid
dates in A1 and B1 as it does no checking. Excel maintains
dates
and
times
as numbers. The integral part of the number is the day, the
fractional
part
the time. For example Feb. 1, 2008 12:00 PM, noon, is 39479.5
Jan.
1,
1900
is day 1. So Feb 1, 2008 is day 39,479. The .5 represents
12/24.
12:00
AM,
midnight, is 0.0 3:00 AM is 3/24 = 0.125 3:00 PM is 15/24 =
0.625
To
see
the numbers, press Ctrl+Accent Grave (top row, leftmost key).
Press
Ctrl+Accent Grave to return to normal display.

Tyro

"adimar" wrote in message
...

For the data below B1A1 is true.

A B
12/3/07 11:48 AM 12/3/07 1:26 PM

How do I check the dates are at least 1 day apart, or 24
hours
apart?
I am looking for a check to inlcude in a sumproduct, using
external
sheet
reference, like:

SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001))

where C and D contain Scheduled date and Shipped date.

Also I am trying to avoid reformatting the source data or
adding
suplementary, intermediate columns.

Thank you.

Date comparison
 

I just learned how to use the evaluate formula tool and solved this one.
Thank you for your help.

"Tyro" wrote:

Assuming your dates occupy the first 100 rows, cells in AF1:AF100 that are
truly empty will not be greater than a date. However, cells with the empty
string will be greater than a date. So use this formula

=SUMPRODUCT(--(AF1:AF100AD1:AD100))-COUNTIF(AF1:AF100,"="&"").

Tyro


"adimar" wrote in message
...
The columns I'm comparing (AD and AF) are dates generated by an Oracle
report.
I assume they are "", when not a regular/valid date.

I have these types of situations:

AD AF
9/30/2007 12/21/2007
10/18/2007

That is, AF could be "" but all ADs are valid dates.
I need to count how many records have AFAD, when AF<"".

Thank you.
================

"Tyro" wrote:

Or if you have a formula that returns the empty string, which looks like
a
blank, and you subtract 1 from that, you'll also get #VALUE

Tyro

"Tyro" wrote in message
t...
Your problem is elsewhere. If a cell is blank and you subtract 1 from
it,
you get -1. If a cell contains text, and you subtract -1 from it you
get
#VALUE
Do you have some cells containing text in your range?

Tyro

"adimar" wrote in message
...

Yes, that's correct - I have some 4600 records in RawData (equal to
number
of ADs) and some 3000 non-blank AFs.

How then can I change the formula to allow for blank date fields in
AF.
ADs
should always be non-blank but if there's a way to check for that,
that
would
be great.


Thank you.

"Tyro" wrote:

=SUMPRODUCT(--($AF$1:$AF$10000<"") should be
=SUMPRODUCT(--($AF$1:$AF$10000<"")) or even
=COUNTIF($AF$1:$AF$10000,"<"&"")

Tyro

"Tyro" wrote in message
t...
Looks like in $AF$1:$AF$10000 you have non-numerics so that when
the
"-1"
is attempted, Excel sees a #value error. Do a
=COUNT($AF$1:$AF$10000)
to
see how many numeric cells there are and do a
=SUMPRODUCT(--($AF$1:$AF$10000<"") to see how many non-blank cells
there
are. If you have only numbers in that range, both functions should
produce
the same number. If they don't you have some non-numerics in your
range.

Tyro


"adimar" wrote in message
...

This one works:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000RawData!$AD$1:$AD$10000)*((ISNUMBER( MATCH(RawData!$E$1:$E$10000,Definitions!$B$3:$B$6, 0)))))

This one returns #VALUE error, see next to last multiplier:
=SUMPRODUCT((RawData!$B$1:$B10000=40)*(RawData!$AA $1:$AA$10000={"aa","bb","cc"})*(RawData!$AG$1:$AG$ 10000="CLOSED")*(RawData!$P$1:$P$10000="CLOSED")*( RawData!$AD$1:$AD$10000=CalculatedData!F2)*(RawDa ta!$AD$1:$AD$10000<=CalculatedData!F3)*(RawData!$A F$1:$AF$10000-1RawData!$AD$1:$AD$10000)*((ISNUMBER(MATCH(RawDat a!$E$1:$E$10000,Definitions!$B$3:$B$6,0)))))


Thank you.

"Tyro" wrote:

What is the whole formula?

Tyro

"adimar" wrote in message
...

Thank you for the explanation. Yes, this makes sense and works
for
a
quick
test I did.

But I have one example that I don't know how to fix.
Since this one works fine:
RawData!$AF$1:$AF$10000RawData!$AD$1:$AD$10000

Why does this return a #VALUE error?
RawData!$AF$1:$AF$10000-1RawData!$AD$1:$AD$10000

Thank you.


"Tyro" wrote:

You can subtract the dates. For example =IF(B1-A1=1,"Dates
are
at
least
1
day apart","Dates are the same day"). This formula assumes
there
are
valid
dates in A1 and B1 as it does no checking. Excel maintains
dates
and
times
as numbers. The integral part of the number is the day, the
fractional
part
the time. For example Feb. 1, 2008 12:00 PM, noon, is 39479.5
Jan.
1,
1900
is day 1. So Feb 1, 2008 is day 39,479. The .5 represents
12/24.
12:00
AM,
midnight, is 0.0 3:00 AM is 3/24 = 0.125 3:00 PM is 15/24 =
0.625
To
see
the numbers, press Ctrl+Accent Grave (top row, leftmost key).
Press
Ctrl+Accent Grave to return to normal display.

Tyro

"adimar" wrote in message
...

For the data below B1A1 is true.

A B
12/3/07 11:48 AM 12/3/07 1:26 PM

How do I check the dates are at least 1 day apart, or 24
hours
apart?
I am looking for a check to inlcude in a sumproduct, using
external
sheet
reference, like:

SUMPRODUCT((SourceData!$A$1:$A$1000="Completed")*( ShippingData!$C$1:$C$1000-ShippingData!$D$1:$D$100001))

where C and D contain Scheduled date and Shipped date.

Also I am trying to avoid reformatting the source data or
adding
suplementary, intermediate columns.

Thank you.