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.

 

[JamesW]

Hi,

You need to put the date in speech marks ( " " ), otherwise you are doing 31 divided by 3, divided by 2011, which is 0.00513840543676446!

So:

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

 

[rduke]

How about if I am referencing a cell?

 

[T. Valko]

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.

Use a cell to hold the date criteria:

• A1 = 31/3/2011

Then refer to that cell like this:

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

Or, more simply:

=--('[Delivery Redelivery Databse HGN.xls]DEL'!$I1083>A1)

 

[JamesW]

As in the date is in a cell?

In which case:

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

Where B1 is your date.

 

[CharlesChuckieCharles]

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.

maybe try
=IF('[Delivery Redelivery Databse HGN.xls]DEL'!$I1083>DATE(2011,03,31),1,0)

 

[rduke]

Thx

 

[rduke]

The value =cell Delivery Redelivery Databse HGN.xls]DEL'!$I1083 is 01/04/2011 9:36:00 PM

The value =cell A3 is 31/03/2011 11:59:00 PM

The value =cell A4 is 01/04/2011

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

The value returned is 0, it should be 1

help please

 

[T. Valko]

The value =cell Delivery Redelivery Databse HGN.xls]DEL'!$I1083 is 01/04/2011 9:36:00 PM

The value =cell A3 is 31/03/2011 11:59:00 PM

The value =cell A4 is 01/04/2011

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

The value returned is 0, it should be 1

help please

Try it like this...

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

 

[rduke]

Thank you