Results 1 to 3 of 3

Thread: IF Statement using Networkdays.intl formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Location
    Florida
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default IF Statement using Networkdays.intl formula

    I am trying to build a formula that will calculate after determining if a start and end date are equal to or between a calendar (fiscal) start and end date.

    The formula I have is:
    =IF(AND($J$9<=N$4,$K$9>=N$5)*1=1,(N$6*$L$9),IF(AND($J$9<=N$4,$K$9>=N$5)*1=0,IF(AND($J$9<=N$4,$K$9>=N$5)*1=0,(NETWORKDAYS.INTL(N$4,$K$9,1,Holidays)*$L$9),0)))

    My worksheet looks like this:

    J K L M N O P Q R S T U V
    5 Fiscal Start 9/20/19 9/28/19 10/1/19 10/26/19 11/1/19 11/23/19 12/1/19 1/1/20 1/25/20
    6 Fiscal End 9/27/19 9/30/19 10/25/19 10/31/19 11/22/19 11/30/19 12/31/19 1/24/20 1/31/20
    7 Net Wrk Days 6 1 18 4 15 4 21 16 75
    8 POPS POPE Wrk Hrs
    9 09/20/19 11/03/19 8 Program Mgr 48 8 144 32 8 -112 -144 -312

    The first part of the formula is calculating as desired, however, I am not getting a "0" result once the POP dates are out of range of the fiscal start and end dates.

    Can someone please advise how to fix the formula or suggest another formula to get the desired data?

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: IF Statement using Networkdays.intl formula

    Hi,

    try something like this:

    JKLMNOPQRSTUV
    5Fiscal Start20-9-201928-9-20191-10-201926-10-20191-11-201923-11-20191-12-20191-1-202025-1-2020
    6Fiscal End27-9-201930-9-201925-10-201931-10-201922-11-201930-11-201931-12-201924-1-202031-1-2020
    7Net Wrk Days61184154211675
    8POPSPOPEWrk Hrs
    920-9-20193-11-20198Program Mgr488144328-112-144-312
    104881523280000

    Sheet1



    Worksheet Formulas
    CellFormula
    N10=IF(OR(N6<$J9,$K9<N5),0,NETWORKDAYS.INTL(MAX($J9,N5),MIN($K9,N6),1,))*$L9


    Last edited by jorismoerings; Aug 25th, 2019 at 09:04 AM.
    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

    Please follow the forum Rules and Guidelines and use Code tags around your VBA code.

    Remember: test VBA code always on a copy of your file because usually you can't use <undo>

  3. #3
    New Member
    Join Date
    Aug 2019
    Location
    Florida
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF Statement using Networkdays.intl formula

    Thank you so much!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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