if statement

[rduke]

I have a spreadsheet that records the date of when an event happens. I have another spreadsheet that I want to count when an event happens in the month of April.

I tried this and it gives me a result of 1 even if the the event happen before the month in question.

=IF('[Delivery Redelivery Databse HGN.xls]DEL'!$I1083>31/3/2011,1,0)

This formula returns a valve of 1 even if the cell that contains the information is not greater than the date in the formula.

 

[T. Valko]

Thank you

You're welcome!

 

[rduke]

=IF(AND('[Delivery Redelivery Databse HGN.xls]DEL'!$I1083>$A$3,INT('[Delivery Redelivery Databse HGN.xls]DEL'!$I1083)<=$A$4), 1, 0)

it worked for 1083 (value returned was 10
it did not work for call 1083
the value in cell 1084 was 07/04/2011 12:00:00 PM

 

[rduke]

sorry the valve returned for cell 1083 was 1 not 10

 

[rduke]

Sorry for the confusion.

IF(AND('[Delivery Redelivery Databse HGN.xls]DEL'!$I1083>$A$3,INT('[Delivery Redelivery Databse HGN.xls]DEL'!$I1083)<=$A$4), 1, 0)

it worked for 1083 (value returned was 1

it did not work for call 1084
the value in cell 1084 was 07/04/2011 12:00:00 PM

 

[T. Valko]

Sorry for the confusion.

IF(AND('[Delivery Redelivery Databse HGN.xls]DEL'!$I1083>$A$3,INT('[Delivery Redelivery Databse HGN.xls]DEL'!$I1083)<=$A$4), 1, 0)

it worked for 1083 (value returned was 1

it did not work for call 1084
the value in cell 1084 was 07/04/2011 12:00:00 PM

How about explaining in words what you're trying to do?


You say you have:

• A3 = 3/31/2011 11:59:00 PM (m/d/y format)
• A4 = 4/1/2011 (m/d/y format)

The difference between those two values is 1 minute.

Are you wanting to know if cell I1083 falls within that 1 minute time frame?

 

[rduke]

I want to check a range of cells and count if a event occurs in the month of April

 

[T. Valko]

I want to check a range of cells and count if a event occurs in the month of April

Ok, there are many ways to that.

Here's one way...

A3 = 4/1/2011 (m/d/y format)
A4 = 4/30/2011 (m/d/y format)

You don't need the IF function. This will return a result of 1 or 0.

=--AND('[Delivery Redelivery Databse HGN.xls]DEL'!$I1083>=$A$3,'[Delivery Redelivery Databse HGN.xls]DEL'!$I1083<=$A$4)

 

[CharlesChuckieCharles]

How about if( month(I1083)=month($A$3), "bingo", "blah")

 

[rduke]

do you mean type in the cell as shown

=AND('[Delivery Redelivery Databse HGN.xls]DEL'!$I1083>=$A$3,'[Delivery Redelivery Databse HGN.xls]DEL'!$I1083<=$A$4)

when i do I get a false returned in the cell

 

[T. Valko]

Ok, there are many ways to that.

Here's one way...

A3 = 4/1/2011 (m/d/y format)
A4 = 4/30/2011 (m/d/y format)

You don't need the IF function. This will return a result of 1 or 0.

=--AND('[Delivery Redelivery Databse HGN.xls]DEL'!$I1083>=$A$3,'[Delivery Redelivery Databse HGN.xls]DEL'!$I1083<=$A$4)

do you mean type in the cell as shown

=AND('[Delivery Redelivery Databse HGN.xls]DEL'!$I1083>=$A$3,'[Delivery Redelivery Databse HGN.xls]DEL'!$I1083<=$A$4)

when i do I get a false returned in the cell

You didn't include the "--" at the beginning of the formula. Can you not see them in my replies?

=--AND(...)

The "--" will convert the TRUE and FALSE to 1 and 0.

--TRUE = 1
--FALSE = 0