+ Reply to Thread
Results 1 to 16 of 16

Networkdays.intl and IF, how to leave cells with no value blank until populated

  1. #1
    Registered User
    Join Date
    11-07-2018
    Location
    Bristol, England, UK
    MS-Off Ver
    Excel 2010
    Posts
    7

    Smile Networkdays.intl and IF, how to leave cells with no value blank until populated

    Hi all Excelers

    I have a problem which I really need help with, pretty please.

    I shall try and be as concise as possible

    B3 contains a date, I am using a formula to calculate how long a piece of data is missing from D3. So far I am using this formula: =IF(ISBLANK(D3),NETWORKDAYS.INTL(B3,TODAY(),1,'Bank Holidays '!$A$1:$A$8),"")

    this works well in effect - however - for cells further down that have not yet had their date populated (inside B column), a long number is entered (31002) to the cell which the above formula is entered to (0 column).

    what I need and want is for O column cells to remain empty until the date in B3 is entered for the formula to then do its magic.

    Any advice would be great, thank you, Jord

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Networkdays.intl and IF, how to leave cells with no value blank until populated

    Sounds an odd way to do things.

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Networkdays.intl and IF, how to leave cells with no value blank until populated

    You can do this:

    =IF(B3="","",your_existing_formula)

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    11-07-2018
    Location
    Bristol, England, UK
    MS-Off Ver
    Excel 2010
    Posts
    7
    Hey there, this worked perfectly and actually allowed me to further my knowledge of excel. Thank you! I have been days trying to get around this.



    Quote Originally Posted by Pete_UK View Post
    You can do this:

    =IF(B3="","",your_existing_formula)

    Hope this helps.

    Pete

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Networkdays.intl and IF, how to leave cells with no value blank until populated

    You're welcome - glad to help.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  6. #6
    Registered User
    Join Date
    11-07-2018
    Location
    Bristol, England, UK
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Networkdays.intl and IF, how to leave cells with no value blank until populated

    Oh golly, I thought this solved my problem. However for cells that are not yet populated with data (and thus not fulfilling the value) its trying to look for something that isn’t there and generating a long number. Any ideas? I reverted back to: =IF(C1="",networkdays.intl,(A1,TODAY(),1,(rest of formula above)

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Networkdays.intl and IF, how to leave cells with no value blank until populated

    Please provide a sample sheet as previously requested.

  8. #8
    Registered User
    Join Date
    11-07-2018
    Location
    Bristol, England, UK
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Networkdays.intl and IF, how to leave cells with no value blank until populated

    It won’t allow me to post the link to my workbook here as I’m a fairly new user

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Networkdays.intl and IF, how to leave cells with no value blank until populated

    You can't post links here until you have 10 posts, but you can attach your file here even if you have fewer posts.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon (Attachments button), as it does not work on this forum.

    Hope this helps.

    Pete

  10. #10
    Registered User
    Join Date
    11-07-2018
    Location
    Bristol, England, UK
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Networkdays.intl and IF, how to leave cells with no value blank until populated

    Perfect it should be attached now. Thanks for that. You shall see my problem lies in columns D and E.
    This is a referral spreadsheet that is slowly entered as the month goes on, therefore the first date in column A isn’t always there until a date is entered. See what you think and thank you once again.




    Quote Originally Posted by Pete_UK View Post
    You can't post links here until you have 10 posts, but you can attach your file here even if you have fewer posts.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon (Attachments button), as it does not work on this forum.

    Hope this helps.

    Pete
    Attached Files Attached Files

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Networkdays.intl and IF, how to leave cells with no value blank until populated

    Try this:

    =IF(B2="","",IF(C2="",NETWORKDAYS.INTL(B2,TODAY(),1,Sheet2!$A$1:$A$6),""))

    The "long number" was the number of working days since 0th January 1900, when Excel's date system starts. Excel interpreted the blank in column B as being a date of day zero.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    11-07-2018
    Location
    Bristol, England, UK
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Networkdays.intl and IF, how to leave cells with no value blank until populated

    I see what you mean, the only problem is I only want a working days wait if there is no accepted date and no first visit date. if the date is entered then I do not wish to see a value in the days wait columns

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Networkdays.intl and IF, how to leave cells with no value blank until populated

    Ca we see a sheet with about 10 rows and manually calculated results (no non-working formulae)

  14. #14
    Registered User
    Join Date
    11-07-2018
    Location
    Bristol, England, UK
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Networkdays.intl and IF, how to leave cells with no value blank until populated

    here is an exact replica of the sheet, with no confidential data there of course.
    I need to know the working days wait for accepted date and first visit wait

    thank you once more
    Attached Files Attached Files

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Networkdays.intl and IF, how to leave cells with no value blank until populated

    Try this, in N3:
    =IFERROR(IF([@[Accepted Date ]]="","",NETWORKDAYS.INTL([@[Referral Date ]],[@[Accepted Date ]],1,'Bank Holidays '!$A$1:$A$6)),"")

    and in O3:
    =IFERROR(IF([@[Assessment Date ]]="","",NETWORKDAYS.INTL([@[Referral Date ]],[@[Assessment Date ]],1,'Bank Holidays '!$A$1:$A$6)),"")
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    02-25-2021
    Location
    Bahia, Brasil
    MS-Off Ver
    2019
    Posts
    1

    Re: Networkdays.intl and IF, how to leave cells with no value blank until populated

    Olá Pessoal, muito grato pelas preciosas informações compartilhadas e sua dedicação de tempo prol do desenvolvimento do ser humano.

+ 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. =networkdays.intl
    By Southie89 in forum Excel General
    Replies: 5
    Last Post: 05-11-2018, 11:07 AM
  2. Flexible holidays in NETWORKDAYS.INTL()
    By Rionce in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 01-04-2018, 03:11 PM
  3. NETWORKDAYS.INTL in 2007
    By nickh1981 in forum Excel General
    Replies: 4
    Last Post: 08-11-2015, 02:45 AM
  4. [SOLVED] Networkdays.intl
    By andrecamapum in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-30-2015, 09:31 AM
  5. Replies: 3
    Last Post: 02-09-2015, 09:00 AM
  6. [SOLVED] Networkdays.Intl, incorrect result in one row
    By razaas in forum Excel General
    Replies: 1
    Last Post: 10-22-2014, 07:04 AM
  7. [SOLVED] Looking for alternative to NETWORKDAYS.INTL
    By Jaron_t in forum Excel General
    Replies: 4
    Last Post: 05-21-2012, 04:54 PM

Tags for this Thread

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