+ Reply to Thread
Results 1 to 3 of 3

Code for IF a Range of Cells contains EOMONTH then populate other with Formula

  1. #1
    Registered User
    Join Date
    01-11-2021
    Location
    Cleveland
    MS-Off Ver
    10
    Posts
    63

    Code for IF a Range of Cells contains EOMONTH then populate other with Formula

    I am trying to create a code that will identify if a range of cells contains an EOMONTH date then another cell will populate with a formula.
    Here is the formula I need to populate into the other cell. Can anyone tell me what's wrong with the formula?

    Please Login or Register  to view this content.
    Last edited by ffbaker81; 01-20-2021 at 03:17 PM.

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Code for IF a Range of Cells contains EOMONTH then populate other with Formula

    =IFS(B16:O16,EOMONTH),(AND(TODAY()-F27>365,TODAY()-F27<10*365),1,AND(TODAY()-F27>10*365),1.5,AND(TODAY()-F27>90),0.5,AND(TODAY()-F27<90),0)

    The IFS function call ends immediately after EOMONTH.

    I figure you mean something like IFS(SUMPRODUCT(--(DAY(B16:O16+1)=1)),.... That is, if a date in B16:O16 is the end of a month, then that date plus 1 day would be the first of the next month.

    Just noticed problems with other parentheses as well as almost certainly excluding cases in which TODAY()-F27 equals 90, 365, etc. In any event, you should use LOOKUP rather than IFS.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Actually, you could even use MATCH
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The "?" as 3rd argument to IF returns the text ? if no date in B16:O16 is at the end of a month. Unclear what you'd want in that case.
    Last edited by hrlngrv; 01-20-2021 at 03:44 PM. Reason: addendum

  3. #3
    Registered User
    Join Date
    01-11-2021
    Location
    Cleveland
    MS-Off Ver
    10
    Posts
    63

    Re: Code for IF a Range of Cells contains EOMONTH then populate other with Formula

    That's perfect, I can't tell you how much I appreciate it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. edit in code for populate range of cell in list box and code for delete any items
    By mazan2010 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2017, 03:33 PM
  2. [SOLVED] Formula needed to auto populate in a range of cells on mult sprdsheets based on text input
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-31-2015, 06:40 PM
  3. [SOLVED] Formula or function to populate a range of cells based on "TODAY()"?
    By Bonzopookie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-29-2014, 05:59 PM
  4. auto populate a formula for rolling range of cells
    By rbtfinley in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-28-2014, 08:54 PM
  5. [SOLVED] Creating a formula to populate range of cells between dates based on start and end dates
    By Rainmain82 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-18-2014, 07:55 AM
  6. Replies: 2
    Last Post: 01-26-2011, 11:16 AM
  7. code with eomonth problems
    By Gary Keramidas in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-02-2006, 11:20 PM

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