IF function not working with dates and NETWORKDAYS

Hi All,

I am at an impass with this, I am trying to get an IF function to show a certain value if True or False depending on whether or not it is before a certain date.

sounds simple....the issue I am having is that it just seems to ignore the logical test and just show the value for False regardless of date.

this is my current formula:

=IF(B9<DATE(2013,1,7),INT(NETWORKDAYS(B9,B11,Data!C9:C46)/5),INT(NETWORKDAYS(B9,B11,Data!C9:C38)/5))

It will probably be something really basic that i have missed but i would really appreciate any assitance.

Thanks in advance

Dan

Originally Posted by martindwilson

what does
=B9<DATE(2013,1,7)
give true or false?
what happens when you format b9 as general?

Hi,

thanks for responding

It comes up as false.

It was originally set to date but has since been changed to general during my efforts to resolve the issue.

Originally Posted by martindwilson

what does
=B9<DATE(2013,1,7)
give true or false?
what happens when you format b9 as general?

Hi,

thanks for responding

It comes up as false.

It was originally set to date but has since been changed to general during my efforts to resolve the issue.

Originally Posted by martindwilson

what value does b9 show when formatted general

It currently shows 10/09/2012 as this is the current date that I am using.

Originally Posted by martindwilson

well that's your problem..that is a text date if it was a real date it would show as 41162 which is the date code for 10/09/2012
text is always greater than a number hence FALSE
either delete b9 change the format to general and re-nter the date (it should then get recognised and format itself automatically) or if you have a column of dates in the same format
select the column
go to data tab
text to columns
click next
click next again
and assuming dates are d/m/y
choose date option and use the drop down to set to DMY
click finish
now you should have real dates

That makes sense. I probably should have mentioned that I am using a date picker to populate B9. If i remove what is in B9 it just repopulates it using the same formatting that is causing the issue...is there a way i can change this?

Originally Posted by martindwilson

try using
DATEVALUE(B9) instead

That did the trick!

Thank you very much