IF formula with Workday condition
Results 1 to 4 of 4

Thread: IF formula with Workday condition
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2018
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default IF formula with Workday condition

    Hi,

    In Column A I have dates, and in Column B I have text ‘LARGE’ or SMALL’.

    In Column C I want to reference the date from Column A with the condition that if the text in Column B is ‘SMALL’ then it adds 60 workdays taking into account a holiday range in Column D cells D1:D7. If the text is ‘LARGE’ then the date is reference as stated.

    Many thanks for help with the formula. 😀

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,480
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: IF formula with Workday condition

    How about:

    ABCD
    1DateSizeNew Date1/1/2019
    25/4/2019Large5/4/20191/27/2019
    32/2/2019Small4/26/20195/27/2019
    46/15/2019Small9/10/20197/4/2019
    510/1/2019Small12/26/20199/2/2019
    612/15/2019Small3/9/202011/28/2019
    712/15/2019Large12/15/201912/25/2019

    Sheet9



    Worksheet Formulas
    CellFormula
    C2=IF(B2="Large",A2,WORKDAY(A2,60,$D$1:$D$7))

    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    Board Regular
    Join Date
    Sep 2018
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF formula with Workday condition

    Hi Eric, thank you for your help. My criteria is more complex that first thought. Can you help with modifying the below please?


    Date Size Team Hols
    01/04/2019 Large Team 1 01/01/2019
    01/06/2019 Small Team 2 26/08/2019
    01/08/2019 Large Team 3 25/12/2019
    01/10/2019 Small Team 4 26/12/2019

    IF OR Criteria:

    1) Large --- ref current value only
    2) Small AND Team 1 or Small AND Team 2 --- ref current value +60 days
    3) Small AND not Team 1 or Team 2 (any other cell value) --- ref current value only

    Many thanks!
    Last edited by horizonflame; Aug 15th, 2019 at 03:30 AM.

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,480
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: IF formula with Workday condition

    Try:

    ABCDE
    1DateSizeTeamNew DateHolidays
    24/1/2019LargeTeam 14/1/20191/1/2019
    36/1/2019SmallTeam 28/26/20191/27/2019
    48/1/2019LargeTeam 38/1/20195/27/2019
    510/1/2019SmallTeam 410/1/20197/4/2019
    69/2/2019
    711/28/2019
    812/25/2019

    Sheet9



    Worksheet Formulas
    CellFormula
    D2=IF(AND(B2="small",OR(C2={"Team 1","Team 2"})),WORKDAY(A2,60,$E$2:$E$8),A2)

    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

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
  •