+ Reply to Thread
Results 1 to 17 of 17

if statement with networkdays error

  1. #1
    Registered User
    Join Date
    04-24-2020
    Location
    South Africa , Johannesburg
    MS-Off Ver
    Gauteng
    Posts
    21

    if statement with networkdays error

    Hi Guys .

    I intially had the formula working without network days ( Column E) .In (column F) i needed to get the calculation to be done in networkdays. I managed to get the networkdays formula working for the first set of dates(Column A & B) . I need help in getting a statement that says if column A&B have no date value than look at Networkdays within( Column E & A ) and if not date value then state as " Not sent as yet"


    =IFERROR(NETWORKDAYS(A3;B3;I4:I15);IF(ISNUMBER(C3);(C3);NETWORKDAYS(C3;$BK$1;I4:I15)))

    Please help me
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,065

    Re: if statement with networkdays error

    Hi,
    Please review the file and let me know if this is the desired results.
    Attached Files Attached Files
    Last edited by belinda200; 04-24-2020 at 08:42 AM.

  3. #3
    Registered User
    Join Date
    04-24-2020
    Location
    South Africa , Johannesburg
    MS-Off Ver
    Gauteng
    Posts
    21

    Re: if statement with networkdays error

    Yes this works. You are a champion. Thanks

  4. #4
    Registered User
    Join Date
    04-24-2020
    Location
    South Africa , Johannesburg
    MS-Off Ver
    Gauteng
    Posts
    21

    Re: if statement with networkdays error

    Hi Team.

    I have encountered an issue on this file. ( Column BM323 should have stated a zero. ) That is when we calculate network days between column U & X we should have gotten a value. If there was no value then column BM must minus column U to get a number value .

    Please can you help me on this thread. See attached. I highlighted columns in red.

  5. #5
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,065

    Re: if statement with networkdays error

    Hi
    There is no attachment to your message....

  6. #6
    Registered User
    Join Date
    04-24-2020
    Location
    South Africa , Johannesburg
    MS-Off Ver
    Gauteng
    Posts
    21

    Re: if statement with networkdays error

    Hi

    See attached
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,065

    Re: if statement with networkdays error

    That's how excel calculates the days.
    you have to reduce one if you dont want to include in your counting the starting date.

    30-9 = 1
    1.10= 2
    2.10 = 3

  8. #8
    Registered User
    Join Date
    04-24-2020
    Location
    South Africa , Johannesburg
    MS-Off Ver
    Gauteng
    Posts
    21

    Re: if statement with networkdays error

    Hi

    Thanks Belinda. Can you help me by adding it on the excel spread sheet?
    Attached Files Attached Files

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,334

    Re: if statement with networkdays error

    Quote Originally Posted by belinda200 View Post
    Hi,
    Please review the file and let me know if this is the desired results.
    Belinda - you have been asked before to outline the solution offered in the body of the thread as well as in the attachment. Please do so. Thank you for your co-operation.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  10. #10
    Registered User
    Join Date
    04-24-2020
    Location
    South Africa , Johannesburg
    MS-Off Ver
    Gauteng
    Posts
    21

    Re: if statement with networkdays error

    Thanks Ali. Apologises for the other thread comment. Did not know how to use the portal.

  11. #11
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,213

    Re: if statement with networkdays error

    Shiraz

    This is a problem with dates. If the start and the end are the same date, should it show 1 or 0 as the duration.
    And then the decision applies to all values above that
    IF a hotel stay just records nights they would be the same for a one day stay, howeverever day of departure - day of arrival would be 1

    you could -1 to all of your formulas or just the part with references BK1 which works out today


    What is the model solution you require, and why would it be 0? bearing in mind today is now 2 days after you first posted? Do you mean 2?

  12. #12
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,065

    Re: if statement with networkdays error

    Sure Ali. I remember that, and making sure to comply since then. since this one was posted on April I have no option to edit now.

  13. #13
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,065

    Re: if statement with networkdays error

    =IF(ISBLANK(U323),"",IF(AND(ISNUMBER(U323)=FALSE,ISNUMBER(X323)=FALSE),"Not sent as yet",IF(AND(ISNUMBER(U323)=TRUE,ISNUMBER(X323)=TRUE),NETWORKDAYS(U323,X323,'Public Holidays'!$A$5:$A$16),IF(AND(ISNUMBER(U323)=TRUE,ISNUMBER(X323)=FALSE,ISNUMBER(BL323)=TRUE),NETWORKDAYS(U323,$BK$1,'Public Holidays'!$A$5:$A$16)-1))))

  14. #14
    Registered User
    Join Date
    04-24-2020
    Location
    South Africa , Johannesburg
    MS-Off Ver
    Gauteng
    Posts
    21

    Re: if statement with networkdays error

    Hi Belinda

    Column BM323 is still not working. It should be 0 days taken and not 1 . See attached. What am I doing wrong ?
    Attached Files Attached Files

  15. #15
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,065

    Re: if statement with networkdays error

    Hi,
    This is the correct formula.
    You placed the (-1) in the wrong location.
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    04-24-2020
    Location
    South Africa , Johannesburg
    MS-Off Ver
    Gauteng
    Posts
    21

    Re: if statement with networkdays error

    Hi Belinda. Any feedback on this thread request. Please add formula to the sheet for me .

  17. #17
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,065

    Re: if statement with networkdays error

    Hi,
    see post #15...gave you the solution
    copy the formula provided into your sheet

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Networkdays function combined with If Statement
    By JOB# in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-10-2020, 01:56 PM
  2. Combining an If Statement with NETWORKDAYS function
    By Shirley Munro in forum Excel General
    Replies: 2
    Last Post: 05-22-2019, 03:19 PM
  3. networkdays with if statement
    By tdrose01 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-23-2014, 02:05 PM
  4. NETWORKDAYS error
    By Wildways in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-23-2014, 01:09 PM
  5. How to write and IF AND statement with a NOW date and NETWORKDAYS
    By Tpulcino in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-15-2013, 11:55 AM
  6. Replies: 2
    Last Post: 03-15-2013, 10:56 AM
  7. Replies: 3
    Last Post: 09-24-2009, 07:19 PM

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