#NUM error in IF statement using dates 1

[ATylman]

I get a #NUM error from my If statement when the logical test is False (or true if I swap the test) but it works fine when True.

=IF(B1<&quot;3/7/1983&quot;,DATEDIF(B1,&quot;3/7/1983&quot;,&quot;d&quot;),&quot;Arrgh I'm going mad&quot;)

Any ideas would be much appreciated as I am at wits end.

 

[KenWright]

Pound to a penny that in the following:-

=Datedif(date1,date2,"d&quot

your date1 is greater than your date2 which will return a #NUM error. You need to switch your formula if that is what the case is going to be, eg:-

=IF(B1<&quot;3/7/1983&quot;,DATEDIF(&quot;3/7/1983&quot;,B1,&quot;d&quot,&quot;No you're not - honest&quot

You may need to tweak it to give you what you needed

Chip Pearson has a site that will tell you all about datedif:-

http://cpearson.com/excel/datedif.htm

Regards
Ken...............

 

[ATylman]

Sorry, I didn't give enough info in the initial post.
I believe it's the If statement that's going haywire ie. In the following code

=IF(B1<&quot;31/7/1983&quot;,DATEDIF(B1,&quot;31/7/1983&quot;,&quot;d&quot;),&quot;Arrgh I'm going mad&quot;)

if I have 30/7/1983 in B1 then it returns 1. However if I have 1/8/1983 then it returns #NUM. Similarly, when I put in the formula

=IF(B1<&quot;31/7/1983&quot;,&quot;Yes&quot;,&quot;No&quot;)

then it will only return Yes and acts as if it does not see the False result.

 

[GlennUK]

Try putting DATEVALUE around your date strings.

=IF(B1<DATEVALUE(&quot;31/7/1983&quot,&quot;Yes&quot;,&quot;No&quot

works fine for me.

Cheers, Glenn.

 

[ATylman]

Cheers Glen, worked like a charm!