Date value content format

  • Thread starter Thread starter adimar
  • Start date Start date
A

adimar

I have the data below in different spreadsheets. I'm attempting to debug
"sumproduct" errors on one ofthe sheet. Tha same formula works fine in
another sheet.

=SUMPRODUCT((H3:H32="CLOSED")*(G3:G32>E3:E32))

11/19/07 0:00 11/19/07 0:00 11/2/07 19:10
11/19/07 0:00 11/12/07 23:59 11/12/07 16:25
11/19/07 0:00 11/12/07 23:59 11/12/07 16:25
11/26/07 0:00 11/21/07 23:59 11/20/07 15:47
11/26/07 0:00 11/20/07 23:59 11/20/07 16:05
11/26/07 0:00 11/21/07 23:59 11/20/07 17:03
 
Problem: The formula below returns correct value in one sheet and zero in
another, on what "seems" to be the same data. How do I check date cell
content is really the same in different sheets?

SUMPRODUCT((H3:H32="CLOSED")*(G3:G32>E3:E32))

I am looking for a correct way of comparing dates.


Thank you.
 
There must be a difference in the data. Things to look for:

1. Do you have "CLOSED" in at least one of the cells in H3:H32?
2. Is at least one of the cells in G3:G32 greater than E3:E32?
3. Are G3:G32 and E3:E32 true dates, or are they text masquerading as dates?
(Easy test -- try to change the format of a cell)?

If the formula works on one spreadsheet, but not on another, then the data
must be different.

Regards,
Fred
 
On this data,
B C D
E
12/3/07 12:00 AM 12/26/07 4:18 PM CLOSED TRUE
12/3/07 12:00 AM 11/30/07 5:51 PM CLOSED FALSE
12/6/07 12:00 AM 11/16/07 4:37 PM CLOSED FALSE
11/19/07 12:00 AM 11/22/07 7:10 PM CLOSED TRUE
11/19/07 12:00 AM 11/12/07 4:25 PM CLOSED FALSE
11/19/07 12:00 AM 11/24/07 4:25 PM CLOSED TRUE
11/26/07 12:00 AM 11/20/07 3:47 PM CLOSED FALSE
11/26/07 12:00 AM 11/29/07 4:05 PM CLOSED TRUE
11/26/07 12:00 AM 11/20/07 5:03 PM CLOSED FALSE
12/7/07 10:16 PM 11/30/07 5:08 PM CLOSED FALSE

I ran these formulas with these results:

Formula
Result
=C22>B22
TRUE/FALSE as above
=COUNTIF(D22:D31, "CLOSED")
10
=SUMPRODUCT((D22:D31="CLOSED")*1) 10
=SUMPRODUCT((C22:C31>B22:B31)*(C22:C31="CLOSED")) 0
=SUMPRODUCT(C22:C31>B22:B31) 0

First 2 are correct, last 2 are not.
Copied to a blank workbook and got the same results.
Also copied text from window above and pasted special, text. Same result.
What else can I try?

Thank you.
 
In the second formula, the range is wrong. It should be (D22:D31="CLOSED"),
rather than C22:C31.

In the third formula, you are not converting the true/false results to a
number. Either multiply by 1 (as you did in the first formula), or use the
more common double unary (--) to force the conversion.

Hope this helps,
Fred.
 
Works fine now... Thank you.

Fred Smith said:
In the second formula, the range is wrong. It should be (D22:D31="CLOSED"),
rather than C22:C31.

In the third formula, you are not converting the true/false results to a
number. Either multiply by 1 (as you did in the first formula), or use the
more common double unary (--) to force the conversion.

Hope this helps,
Fred.
Click to expand...
 
Back
Top