+ Reply to Thread
Results 1 to 10 of 10

IF function not working with dates and NETWORKDAYS

  1. #1
    Registered User
    Join Date
    02-07-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    6

    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

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: IF function not working with dates and NETWORKDAYS

    what does
    =B9<DATE(2013,1,7)
    give true or false?
    what happens when you format b9 as general?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    02-07-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: IF function not working with dates and NETWORKDAYS

    Quote Originally Posted by martindwilson View Post
    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.

  4. #4
    Registered User
    Join Date
    02-07-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: IF function not working with dates and NETWORKDAYS

    Quote Originally Posted by martindwilson View Post
    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.

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: IF function not working with dates and NETWORKDAYS

    what value does b9 show when formatted general

  6. #6
    Registered User
    Join Date
    02-07-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: IF function not working with dates and NETWORKDAYS

    Quote Originally Posted by martindwilson View Post
    what value does b9 show when formatted general
    It currently shows 10/09/2012 as this is the current date that I am using.

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: IF function not working with dates and NETWORKDAYS

    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

  8. #8
    Registered User
    Join Date
    02-07-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: IF function not working with dates and NETWORKDAYS

    Quote Originally Posted by martindwilson View Post
    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?

  9. #9
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: IF function not working with dates and NETWORKDAYS

    try using
    DATEVALUE(B9) instead

  10. #10
    Registered User
    Join Date
    02-07-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: IF function not working with dates and NETWORKDAYS

    Quote Originally Posted by martindwilson View Post
    try using
    DATEVALUE(B9) instead
    That did the trick!

    Thank you very much

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1