+ Reply to Thread
Results 1 to 10 of 10

Return a value based on a range of dates

  1. #1
    Registered User
    Join Date
    10-18-2018
    Location
    Manchester, England
    MS-Off Ver
    2013
    Posts
    5

    Question Return a value based on a range of dates

    Hi, I'm trying to return a specific value/word if a date is prior to today’s date in a range of dates. If I have 5 different dates after today then I want a cell to say "Approved" if 4 of the dates are after today and one is prior then I want the cell to say unapproved. If there are no dates in a specific cell then I want the cell to be blank. I am able to do this for just one specific cell using an IF formula but cannot work it out to look at a range.
    Any help or advice would be appreciated.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Return a value based on a range of dates

    Welcome to the forum!

    You will need to do something like this:

    =LOOKUP(COUNTIF(date_range,">"&TODAY()),{0,5},{"Unapproved","Approved"})
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    10-18-2018
    Location
    Manchester, England
    MS-Off Ver
    2013
    Posts
    5

    Re: Return a value based on a range of dates

    Thank You.

    This appeared to work initially but once I changed some dates it does not appear to be changing the approved or unapproved value. For example I changed two of the dates to January 2018 but the return still says 'Approved'.

    Also I may be over complicating this but not all the cells in the range will necessarily have a date in as they may not be applicable. This formula only works if every cell in the range has a date in. I am unsure if it is possible that some cells in the range are blank.

    To try and give you a little more context I have a spreadsheet of subcontractors who are approved and unapproved by the company I work for. The subcontractors have to send us their relevant evidence to show what insurance/qualifications they hold. For each of these documents I have a column to put the date of expiry. However they do not necessarily need all the qualifications on the spreadsheet, so I need to be able to put N/A and the formula still work. The idea being that any that do have a date are taken into account when returning "Approved" or "Unapproved".

    Apologies if I am not giving enough information or this is unclear. I have never used a forum to get solutions before! Appreciate the help.
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Return a value based on a range of dates

    So how do you want it to work if there are blank cells in the range?

    Your range is 8 cells (columns) wide - how many of these need to be after today to trigger "approved"?

    You are not making it clear how you want this to work: it clearly isn't quite what you asked for originally.

    Please attach the file again. This time, manually enter the results you want to see (get rid of the formula for now) so that I can understand the logic you wish to apply.

  5. #5
    Registered User
    Join Date
    10-18-2018
    Location
    Manchester, England
    MS-Off Ver
    2013
    Posts
    5

    Re: Return a value based on a range of dates

    Apologies.
    Basically any cell that has a date in I would like the formula to take in to account. If there is an "N/A" I would like this to be treated as though it would be in date so to speak.
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Return a value based on a range of dates

    So, if any of the dates in the cells that have a date in them are earlier than today, then it's not approved - is this correct?

  7. #7
    Registered User
    Join Date
    10-18-2018
    Location
    Manchester, England
    MS-Off Ver
    2013
    Posts
    5

    Re: Return a value based on a range of dates

    yes, that's correct.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Return a value based on a range of dates

    How about this?

    =IF(MIN(H6:O6) < TODAY(),"Not Approved","Approved")

    Or:

    =IF(MIN(H6:O6) >=TODAY(),"Approved","Not Approved")

  9. #9
    Registered User
    Join Date
    10-18-2018
    Location
    Manchester, England
    MS-Off Ver
    2013
    Posts
    5

    Re: Return a value based on a range of dates

    That's fantastic thank you!

    I wasn't far off when I first started, I had virtually the same formula but without the 'MIN'. Now it all appears so simple!

    Thank you again for your help.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Return a value based on a range of dates

    You are welcome! Sometimes a fresh pair of eyes is all that's needed.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. VBA auto return dates based on date range
    By Philly500 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-25-2015, 12:36 PM
  2. [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
  3. [SOLVED] Trying to get cells to return a range based on two dates
    By redjayhawk11 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-26-2013, 01:26 PM
  4. [SOLVED] Return a variable based on a selection between dates and times in a range
    By Mr. H2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-20-2013, 10:22 AM
  5. [SOLVED] Return a value between 2 dates within a range of dates
    By tsmor in forum Excel General
    Replies: 1
    Last Post: 03-31-2011, 06:45 PM
  6. Compare Dates To Date Range & Return Date Based On Outcome
    By gusmontoya in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-03-2008, 09:06 PM
  7. [SOLVED] return based on range of dates
    By Matt in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-15-2006, 04:40 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