+ Reply to Thread
Results 1 to 5 of 5

Modifying String

  1. #1
    Registered User
    Join Date
    04-24-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2010
    Posts
    19

    Angry Modifying String

    Hello Everyone!

    I am in a bit of a rut here, and I can't seem to figure out how to do this. I am trying to make a turnaround time calendar, and it needed to include only working days vs the entire week.

    This has been accomplished, but I need to add a condition, so I can also exclude specific days to not count as working days.

    This is the formula that I am using: =IF(ISNUMBER(A24),WORKDAY(A24&LOOKUP("zzz",A$2:A24)&A$1,8),"")

    I need to exclude: May 28, July 4, Sept 3, Nov 22-23, Dec 24-25.

    Where do I add the exception? Your help is greatly appreciated! Infinia Patient Scheduler 2012.xls

  2. #2
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    591

    Re: Modifying String

    Just add an is not equal to condition via AND & OR, so...

    If A24 is a number AND the returned workday via lookup is not equal to daten OR daten2 OR daten3 OR daten4, then return the workday lookup, else...
    Perhaps it was the Noid who should have avoided me...
    If you are satisfied with my solution click the small star icon on the left. Thanks
    1. Make a copy of your workbook and run the following code on your copy (just in case)
    2. With excel open, press ALT+F11 to open the Visual Basic Editor (VBE). From the "Insert" menu, select "Module".
    3. Paste the code from above into the empty white space. Close the VBE.
    4. From the developer tab, choose "Macros", select the Sub Name, and click "Run".

  3. #3
    Registered User
    Join Date
    04-24-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Modifying String

    Wow, thanks for the fast reply.

    Ok, i understand what you're saying, i just have no idea how to actually do that. (Sorry, my experience with Excel really goes about as far as knowing how to use the pre-built equations) Could you possibly type out what I should be doing please? I need to know how to do this, because when 2013 rolls around I'll have to know how to add the date then as well, but could you show me how to do that for the 2012 dates?

    thanks

  4. #4
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    591

    Re: Modifying String

    Actually, I had to dust off my WORKDAY syntax as I haven't used it in a while. The function already makes available the optional argument for holidays as an array constant.

    So as an example, if you have entered each of the days designated as holidays in, say, H2:H8, you simply add that after the 8 and after a comma. With the formula still active, you would highlight that new portion of the formula and press F9.

    Modified and before the array:
    Please Login or Register  to view this content.
    And after transforming the "H" range to array:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-24-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Modifying String

    wow, ok, so I entered the dates as requested and made Column H in date format.

    However I have no idea how to transform "H" into an array. What I did do however, was copy - "{41057;41094;41155;41235;41236;41267;41268})" and paste it right after the 8,

    This works, thank you! I just now have to figure out how how to do this properly now. Thank you so much!!

+ 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