+ Reply to Thread
Results 1 to 5 of 5

If and Then help needed please

  1. #1
    Registered User
    Join Date
    10-26-2012
    Location
    Derbyshire, UK
    MS-Off Ver
    Excel 2010
    Posts
    14

    If and Then help needed please

    Hi All

    I think that I need a piece of VB code that will enable me to do the following:

    If then information in 'cell A' is equal to XX (text taken from a drop down list) then take the date in 'cell B' and add 20 working days to it, if it is equal to YY (again taken from the same drop down list) then take the date in 'cell B' and add 20 working days to it, but if it is equal to ZZ then take the date in 'cell B' and add 40 days to it (not working days).

    I have found 'NETWORKDAYS' in the formulas and messed about with 'IF' statements etc, but don't seem to be able to make a fomula work, so think it will have to be code. As you can see I know exactly what I want the code to say, I just don't have enough experience to write it.

    Big thanks to anyone who is able to offer any help or tips.

    (I realise that no code is going to be able to accommodate for bank holidays!)
    Last edited by AnneFr; 10-29-2012 at 05:43 AM.

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

    Re: If and Then help needed please

    Try this formula in C2:

    =IF(OR(A2="XX",A2="YY),WORKDAY(B2,20),B2+40)

    You can supply an optional 3rd parameter to the WORKDAY function which represents a list of holidays, so you can take account of bank holidays.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    10-26-2012
    Location
    Derbyshire, UK
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: If and Then help needed please

    Hi Pete

    Thanks everso for the forumla - it works fine for the +20 workdays, but seems to take no notice of the second 'then' statement and adds +20 workdays not 40 'ordinary days'.

    Am I missing something vital?

    Thanks again

    Anne

  4. #4
    Registered User
    Join Date
    10-26-2012
    Location
    Derbyshire, UK
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: If and Then help needed please

    Hi Pete

    I was missing something!! Durr - I understand now.

    Thanks

    Anne

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

    Re: If and Then help needed please

    Well, glad to hear it. Please mark the thread as solved if you consider it to be so. (the FAQ describes how to).

    Pete

+ 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