+ Reply to Thread
Results 1 to 3 of 3

Establish range for # of days a case has been open via create date and today's date

  1. #1
    Registered User
    Join Date
    12-18-2018
    Location
    Alpharetta, GA
    MS-Off Ver
    2016
    Posts
    38

    Establish range for # of days a case has been open via create date and today's date

    I would like to create buckets of the numbers of days a case has been open. I have used Create Date and Today's date to get this number but need help creating a formula that displays the range.

    If a case has been open less than 5 days then display "Less than 5 Days"
    If a case has been open between 5-10 days then display "5-10 Days"
    If a case has been open between 10-20 days then display "10-20 Days"
    If a case has been open more than 20 days then display "20+ Days"

    I have created an IF statement but it doesn't seem to work for all values. I have attached a sample.

    Any help would be appreciated!
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Establish range for # of days a case has been open via create date and today's date

    Please try at B4

    =IF(A4<5,"Less than 5 days",IF(A4<10,"5-10 days",IF(A4<20,"10-20 days","20+ days")))
    or
    =LOOKUP(A4,{0,5,11,21},{"Less than 5 days","5-10 days","10-20 days","20+ Days"})

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Establish range for # of days a case has been open via create date and today's date

    Hi Exceling,

    This is a perfect time to use VLookup() TRUE construct. See the attached with this formula to have another method to solve your problem.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    VLookup True for Days Open.xlsx
    Note that I've changed some values in the left table but they work because of the TRUE on VLookup.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say 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. [SOLVED] COUNTIF anumber of entries in a date range 14-30 days before TODAY?
    By HeathWilD in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-31-2015, 05:47 PM
  2. As Today's Date Changes, After 7 Days, The Range of The Lookup Changes
    By Gander_The_Panda in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 05-07-2015, 12:21 PM
  3. Replies: 1
    Last Post: 01-19-2015, 02:05 PM
  4. Replies: 4
    Last Post: 01-17-2013, 01:23 PM
  5. [SOLVED] Macros for: If Today's Date minus other date is greater than certain amount of days Then
    By lottidotti in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-24-2012, 04:46 PM
  6. create a macro to alert me if today's date is within 5 days of expected delivery date
    By ashmcclure in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 09-25-2008, 05:51 PM
  7. How do I add a range by date over 90 days older than today
    By John DeLosa in forum Excel General
    Replies: 4
    Last Post: 02-16-2006, 05:35 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