+ Reply to Thread
Results 1 to 4 of 4

Look at date cell & return 1 of 7 possible values based on how long ago it was

  1. #1
    Registered User
    Join Date
    01-07-2005
    Posts
    20

    Look at date cell & return 1 of 7 possible values based on how long ago it was

    Hi all,

    This is SUCH a great forum. I spend a lot of time lurking & learning here, plus I've gotten excellent help a couple of times. I'm trying to return some values based on a date but going crazy trying to decide which function best suits it & how to nest the possible results correctly.

    I would like cell B1 to look at the date entered in cell A1. If the date is under 30 days old I want B1 to show the result "Recruit". If the date is 30-119 days old I want it to return the result "Member". If it is 120-364 days the result would be "Under 1 Year". If it is 365-729 days old the result would be "1 Year". If it is 731-1094 days old the result would be "2 Years". If it is 1095-1459 days old the result would be "3 years". Same thing for years 4 & 5. Then anything over 5 years would return the result "Over 5 Years".

    Any input is greatly appreciated! And thanks to all those I have gained knowledge from while lurking!

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Look at date cell & return 1 of 7 possible values based on how long ago it was

    =LOOKUP(TODAY()-A1, {-50000,30,365,731,1095}, {"Recruit","Member","1 Year","2 Years","3 Years"})
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    01-07-2005
    Posts
    20

    Re: Look at date cell & return 1 of 7 possible values based on how long ago it was

    shg,
    I replied here but it is not showing for some reason. Huge thanks to you for coming up with a more elegant solution than mine. I wasn't sure what function to use & kept thinking DATEDIF would be best but I was making a mess of it. Thanks for your time & help!

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Look at date cell & return 1 of 7 possible values based on how long ago it was

    You're welcome.

+ 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. Formula to return values based on criteria and date range
    By jr217 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-28-2014, 06:20 AM
  2. [SOLVED] check a date and return different values based on time and day of week
    By garyfahy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-22-2014, 07:26 AM
  3. Return several values based upon date range
    By tsmor in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-06-2014, 08:47 PM
  4. array formula to return sum of values based on date and category code
    By usmc0331 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-18-2013, 08:24 PM
  5. [SOLVED] Return all values based on a date range
    By stupidname in forum Excel General
    Replies: 3
    Last Post: 06-04-2012, 08:49 AM

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