+ Reply to Thread
Results 1 to 4 of 4

Macro that could return a value if criteria falls between date ranges

  1. #1
    Registered User
    Join Date
    08-22-2014
    Location
    PH
    MS-Off Ver
    N/A
    Posts
    2

    Cool Macro that could return a value if criteria falls between date ranges

    Hi All,

    I've been running up and down regarding this topic and cant seem to solve my problem. The thing is I need a macro that If a date falls within a range in column Start Date and End Date in Sheet1, it would return an Amount value in Sheet2. Please see attached image file for reference.

    Thanks in advance!
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Macro that could return a value if criteria falls between date ranges

    if you need a macro for that you posted it in the wrong categorie.

    probably it can also be done with formula.

    in a JPG forummembers can't work.

    so they have to rebuilt your file, to help you.

    probably you get better help if you add an excel file, without confidential information.

    please also add the desired result in your file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    08-22-2014
    Location
    PH
    MS-Off Ver
    N/A
    Posts
    2

    Re: Macro that could return a value if criteria falls between date ranges

    Hi Oeldere,

    Thanks for the advice. I don't know who to contact to so I have tagged Excel formula category. Anyhow, I'm illustrating it below

    Sheet1:
    Staff No scale type Amount Crcy Start Date End Date
    10545245 Default 380,900.00 PHP 4/26/2014 12/31/2014
    10545245 Default 380,900.00 PHP 12/3/2013 4/25/2014
    10545245 Default 380,900.00 PHP 10/1/2013 12/2/2013
    10545245 Default 380,900.00 PHP 9/1/2013 9/30/2013

    Sheet2:
    Staff No Start Date AMOUNT
    10545245 4/21/2013
    10545245 10/22/2013
    10545245 4/23/2013
    10545245 4/24/2013

    I need a macro or a formula wherein if a date entered in StartDate in Sheet2 falls under a date range in Sheet1(StartDate-EndDate), it will locate the Amount value in Sheet1 and display it in Amount col in Sheet2.

    Thanks for the help.

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: Macro that could return a value if criteria falls between date ranges

    Try

    =SUMPRODUCT((Sheet1!E1:E4>=Sheet2!B1)*((Sheet1!F1:F4<=Sheet2!B1)*(Sheet1!C1:C4))
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

+ 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. Replies: 15
    Last Post: 04-08-2013, 12:40 PM
  2. Replies: 2
    Last Post: 01-07-2013, 09:46 AM
  3. [SOLVED] Return value in X if date in Y falls between two dates - Help!
    By nephilim3uk in forum Excel General
    Replies: 4
    Last Post: 07-08-2012, 10:13 AM
  4. [SOLVED] To count if a date falls between two given date ranges
    By ajang in forum Excel General
    Replies: 2
    Last Post: 10-15-2010, 06:07 PM
  5. Return a specified date when it falls within a range....
    By Nokose451 in forum Excel General
    Replies: 1
    Last Post: 01-16-2006, 06:10 PM

Tags for this Thread

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