subtracting dates in an IF statement
 

Dear All

Please, what is wrong with this formula?

=IF(I6="01/01/2099",$Q$2-H6,I6-H6)
....copied down from M6 to M50.

I have two columns of dates (H6,50, I6,50) and a reference date in Q2. All
cells formatted as date (14/03/2001)
The result column is M6,50 (formatted as number | general)
If date in I6 is 01/01/2099, I want to do Q2-H6, if not then I6-H6
If date in I7 is 01/01/2099, I want to do Q2-H7, if not then I7-H7, etc

Formula doesn't seem to recognise the "01/01/2099", but statement valid (no
syntax error)
For example, I7 does contain the date 01/01/2099 and H7 is 12/06/2008, but
formula (in M7) calculates the wrong option (I7-H7=33075 days). Wrong logic?

I am using Excel 2003.


Many thanks, Phil

subtracting dates in an IF statement
 

=IF(I6=Datevalue("01/01/2099"),$Q$2-H6,I6-H6)


"Phil C" wrote:

Dear All

Please, what is wrong with this formula?

=IF(I6="01/01/2099",$Q$2-H6,I6-H6)
....copied down from M6 to M50.

I have two columns of dates (H6,50, I6,50) and a reference date in Q2. All
cells formatted as date (14/03/2001)
The result column is M6,50 (formatted as number | general)
If date in I6 is 01/01/2099, I want to do Q2-H6, if not then I6-H6
If date in I7 is 01/01/2099, I want to do Q2-H7, if not then I7-H7, etc

Formula doesn't seem to recognise the "01/01/2099", but statement valid (no
syntax error)
For example, I7 does contain the date 01/01/2099 and H7 is 12/06/2008, but
formula (in M7) calculates the wrong option (I7-H7=33075 days). Wrong logic?

I am using Excel 2003.


Many thanks, Phil

subtracting dates in an IF statement
 

Joel

Excellent! Many thanks for your prompt response.

Phil


"Joel" wrote in message
...
=IF(I6=Datevalue("01/01/2099"),$Q$2-H6,I6-H6)


"Phil C" wrote:

Dear All

Please, what is wrong with this formula?

=IF(I6="01/01/2099",$Q$2-H6,I6-H6)
....copied down from M6 to M50.

I have two columns of dates (H6,50, I6,50) and a reference date in Q2.
All
cells formatted as date (14/03/2001)
The result column is M6,50 (formatted as number | general)
If date in I6 is 01/01/2099, I want to do Q2-H6, if not then I6-H6
If date in I7 is 01/01/2099, I want to do Q2-H7, if not then I7-H7, etc

Formula doesn't seem to recognise the "01/01/2099", but statement valid
(no
syntax error)
For example, I7 does contain the date 01/01/2099 and H7 is 12/06/2008,
but
formula (in M7) calculates the wrong option (I7-H7=33075 days). Wrong
logic?

I am using Excel 2003.


Many thanks, Phil