Results 1 to 3 of 3

Combine Options for Single Argument

Threaded View

  1. #1
    Registered User
    Join Date
    11-14-2018
    Location
    Appleton, Wisconsin, USA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    2

    Combine Options for Single Argument

    I am trying to use the WORKDAY() function. The third parameter can be holidays to skip. I have generated 7 formulas to determine the 7 holidays of a given year. Thus my formula I would like to look something like the following: =WORKDAY(A1,1,{NewYears,MemorialDay,IndependenceDay,...}) where formulas would take the place of each holiday looking something like DATE(YEAR(A1),7,4) for IndependenceDay or DATE(G1,11,1)+21+CHOOSE(WEEKDAY(DATE(G1,11,1)),4,3,2,1,0,6,5) for Thanksgiving. etc...

    The temporary solution is to create a range elsewhere with those holidays each taking up a cell in some contiguous block where I could instead make the formula look like =WORKDAY(A1,1,Holidays!$A$1:$G$7). Unfortunately, if I'm still using this worksheet 8 years later, the function becomes useless.

    The second channel I pursued was to make the other sheet called "Holiday" have only 7 cells, each determined by the NOW() function [i.e. =DATE(YEAR(NOW()),12,25) for Christmas] Unfortunately, once a new year hits, all the data for the previous year will get shifted in the column referencing the holidays but not the other columns, rendering it again, useless.

    Any suggestions are appreciated. Thank you.

    [Edit: I figured out how to make it work. MAX(WORKDAY(A1,1,{HolidayFormula1}),WORKDAY(A1,1,{HolidayFormula2}),WORKDAY(A1,1,{HolidayFormula3}),...) If any holidays happen to match up, it will return a later date than all the other WORKDAY() Functions. Add in a special case for the day after Thanksgiving and it will work. I'm still in the market for a more "elegant" solution if anyone knows of it, but You can technically consider the problem solved.]
    Last edited by Dimonium_Anonimo; 11-14-2018 at 10:27 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Select and combine 5 different items together maximizing 5 different options
    By holyexcel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-07-2015, 02:27 AM
  2. Replies: 0
    Last Post: 08-26-2014, 08:44 AM
  3. Combine MANY tabs (with single column) into SINGLE tab.
    By nick1000 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-17-2013, 02:31 PM
  4. Excel 2007 : Selecting multiple ranges as a single argument
    By queenofbabes in forum Excel General
    Replies: 2
    Last Post: 10-16-2011, 09:56 AM
  5. if between function as a single argument
    By options in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-07-2008, 08:49 AM
  6. [SOLVED] Using single cell reference as table array argument in Vlookup
    By CornNiblet in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-22-2005, 04:15 AM
  7. Function (array argument, range argument, string argument) vba
    By Witek in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-24-2005, 11:07 AM

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