CSE formula with dates

[pbolon]

I have two columns of data and I want to count the number of times where condition1 AND condition2 are true. Column 1 contains dates in dd.mm.yyyy format, column 2 contains text. I am looking for the occurences of "accepted" that occur before a specific date.

=SUM(IF((D25:D32="accepted")*(A25:A32<"01.02.2002");1;0))

does not work nor does

=SUM(IF(D25:D32="accepted",IF(A25:A32<"01.02.2002",1,0),0))

any ideas?
-P

 

[IML]

A couple of quick questions:

• How does cell A25 appear (05.22.2002)
• What number appears if you change the format to general
  

 

[Aladin Akyurek]

On 2002-08-28 11:35, pbolon wrote:
I have two columns of data and I want to count the number of times where condition1 AND condition2 are true. Column 1 contains dates in dd.mm.yyyy format, column 2 contains text. I am looking for the occurences of "accepted" that occur before a specific date.

=SUM(IF((D25:D32="accepted")*(A25:A32<"01.02.2002");1;0))

does not work nor does

=SUM(IF(D25:D32="accepted",IF(A25:A32<"01.02.2002",1,0),0))

any ideas?
-P

Before the site went down, I jotted down the following:


Why would you choose for such a foreign date format (which I assume to be text) while you want to compute with them?

See if this works:

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0 < SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

which is normally entered.

Caveat. There should be no blank cells in A25:A32.

Otherwise, use the following array formula:

=SUM(IF((D25:D32="accepted")*(SUBSTITUTE(A25:A32,".","/")+0<=SUBSTITUTE("01.02.2002",".","/")+0),1,0))

To array enter a formula, you need to hit control+shift+enter at the same time, not just enter.

I'd like also to see the answer to IML's questions.

 

[pbolon]

On 2002-08-28 12:18, IML wrote:
A couple of quick questions:

• How does cell A25 appear (05.22.2002)
• What number appears if you change the format to general
  

since this is a german version of excel, the format is dayday.monthmonth.yearyearyearyear. If I enter 28.08.2002 (Aug 28th, 2002) and change to general I get 37497.
This message was edited by pbolon on 2002-08-28 23:57

 

[pbolon]

On 2002-08-28 13:50, Aladin Akyurek wrote:

On 2002-08-28 11:35, pbolon wrote:
I have two columns of data and I want to count the number of times where condition1 AND condition2 are true. Column 1 contains dates in dd.mm.yyyy format, column 2 contains text. I am looking for the occurences of "accepted" that occur before a specific date.

=SUM(IF((D25:D32="accepted")*(A25:A32<"01.02.2002");1;0))

does not work nor does

=SUM(IF(D25:D32="accepted",IF(A25:A32<"01.02.2002",1,0),0))

any ideas?
-P

Before the site went down, I jotted down the following:


Why would you choose for such a foreign date format (which I assume to be text) while you want to compute with them?

See if this works:

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0 < SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

which is normally entered.

Caveat. There should be no blank cells in A25:A32.

Otherwise, use the following array formula:

=SUM(IF((D25:D32="accepted")*(SUBSTITUTE(A25:A32,".","/")+0<=SUBSTITUTE("01.02.2002",".","/")+0),1,0))

To array enter a formula, you need to hit control+shift+enter at the same time, not just enter.

I'd like also to see the answer to IML's questions.

This started as somebody else's database and I am trying to extract info from it. I could change all the dates to numbers and then the comparison works fine, I was just hoping there was a way to work with dates since there is utility in keeping it in a date format in terms of date mathematics

 

[Aladin Akyurek]

On 2002-08-28 23:55, pbolon wrote:

On 2002-08-28 13:50, Aladin Akyurek wrote:

On 2002-08-28 11:35, pbolon wrote:
I have two columns of data and I want to count the number of times where condition1 AND condition2 are true. Column 1 contains dates in dd.mm.yyyy format, column 2 contains text. I am looking for the occurences of "accepted" that occur before a specific date.

=SUM(IF((D25:D32="accepted")*(A25:A32<"01.02.2002");1;0))

does not work nor does

=SUM(IF(D25:D32="accepted",IF(A25:A32<"01.02.2002",1,0),0))

any ideas?
-P

Before the site went down, I jotted down the following:


Why would you choose for such a foreign date format (which I assume to be text) while you want to compute with them?

See if this works:

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0 < SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

which is normally entered.

Caveat. There should be no blank cells in A25:A32.

Otherwise, use the following array formula:

=SUM(IF((D25:D32="accepted")*(SUBSTITUTE(A25:A32,".","/")+0<=SUBSTITUTE("01.02.2002",".","/")+0),1,0))

To array enter a formula, you need to hit control+shift+enter at the same time, not just enter.

I'd like also to see the answer to IML's questions.

This started as somebody else's database and I am trying to extract info from it. I could change all the dates to numbers and then the comparison works fine, I was just hoping there was a way to work with dates since there is utility in keeping it in a date format in terms of date mathematics

Are you saying that:

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0 < SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

does not work? If not, what do you get? BTW, you need to replace the list separator "," with ";" in your version of Excel.

 

[pbolon]

On 2002-08-29 01:00, Aladin Akyurek wrote:

On 2002-08-28 23:55, pbolon wrote:

On 2002-08-28 13:50, Aladin Akyurek wrote:

On 2002-08-28 11:35, pbolon wrote:
I have two columns of data and I want to count the number of times where condition1 AND condition2 are true. Column 1 contains dates in dd.mm.yyyy format, column 2 contains text. I am looking for the occurences of "accepted" that occur before a specific date.

=SUM(IF((D25:D32="accepted")*(A25:A32<"01.02.2002");1;0))

does not work nor does

=SUM(IF(D25:D32="accepted",IF(A25:A32<"01.02.2002",1,0),0))

any ideas?
-P

Before the site went down, I jotted down the following:


Why would you choose for such a foreign date format (which I assume to be text) while you want to compute with them?

See if this works:

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0< SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

which is normally entered.

Caveat. There should be no blank cells in A25:A32.

Otherwise, use the following array formula:

=SUM(IF((D25:D32="accepted")*(SUBSTITUTE(A25:A32,".","/")+0<=SUBSTITUTE("01.02.2002",".","/")+0),1,0))

To array enter a formula, you need to hit control+shift+enter at the same time, not just enter.

I'd like also to see the answer to IML's questions.

This started as somebody else's database and I am trying to extract info from it. I could change all the dates to numbers and then the comparison works fine, I was just hoping there was a way to work with dates since there is utility in keeping it in a date format in terms of date mathematics

Are you saying that:

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0< SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

does not work? If not, what do you get? BTW, you need to replace the list separator "," with ";" in your version of Excel.

Brilliant.

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0< SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

or I should say

=SUMMENPRODUKT((WECHSELN(A25:A32;".";"/")+0<WECHSELN("01.02.2002";".";"/")+0)*(D25:D32="accepted"))

works perfectly. don't understand why it works though
This message was edited by pbolon on 2002-08-29 03:33
This message was edited by pbolon on 2002-08-29 03:34

 

[Aladin Akyurek]

On 2002-08-29 03:32, pbolon wrote:

On 2002-08-29 01:00, Aladin Akyurek wrote:

On 2002-08-28 23:55, pbolon wrote:

On 2002-08-28 13:50, Aladin Akyurek wrote:

On 2002-08-28 11:35, pbolon wrote:
I have two columns of data and I want to count the number of times where condition1 AND condition2 are true. Column 1 contains dates in dd.mm.yyyy format, column 2 contains text. I am looking for the occurences of "accepted" that occur before a specific date.

=SUM(IF((D25:D32="accepted")*(A25:A32<"01.02.2002");1;0))

does not work nor does

=SUM(IF(D25:D32="accepted",IF(A25:A32<"01.02.2002",1,0),0))

any ideas?
-P

Before the site went down, I jotted down the following:


Why would you choose for such a foreign date format (which I assume to be text) while you want to compute with them?

See if this works:

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0< SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

which is normally entered.

Caveat. There should be no blank cells in A25:A32.

Otherwise, use the following array formula:

=SUM(IF((D25:D32="accepted")*(SUBSTITUTE(A25:A32,".","/")+0<=SUBSTITUTE("01.02.2002",".","/")+0),1,0))

To array enter a formula, you need to hit control+shift+enter at the same time, not just enter.

I'd like also to see the answer to IML's questions.

This started as somebody else's database and I am trying to extract info from it. I could change all the dates to numbers and then the comparison works fine, I was just hoping there was a way to work with dates since there is utility in keeping it in a date format in terms of date mathematics

Are you saying that:

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0< SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

does not work? If not, what do you get? BTW, you need to replace the list separator "," with ";" in your version of Excel.

Brilliant.

=SUMPRODUCT((SUBSTITUTE(A25:A32,".","/")+0< SUBSTITUTE("01.02.2002",".","/")+0)*(D25:D32="accepted"))

or I should say

=SUMMENPRODUKT((WECHSELN(A25:A32;".";"/")+0<WECHSELN("01.02.2002";".";"/")+0)*(D25:D32="accepted"))

works perfectly. don't understand why it works though
This message was edited by pbolon on 2002-08-29 03:33
This message was edited by pbolon on 2002-08-29 03:34

See the following on SUMPRODUCT if you want to know how this function works:

http://www.mrexcel.com/wwwboard/messages/8961.html