+ Reply to Thread
Results 1 to 9 of 9

How to Group raw data using date ranges: less than 5 days, between 5 and 14 days and older

  1. #1
    Registered User
    Join Date
    07-01-2014
    Location
    Mexico
    MS-Off Ver
    2013
    Posts
    5

    How to Group raw data using date ranges: less than 5 days, between 5 and 14 days and older

    Hello,
    I wonder if you can help me, I need to Group full rows based on "Open date" column and create 3 groups:
    less than 5 days
    between 5 and 14 days
    older than 14 days
    Please check the attached example

    Classification sample.xlsx

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to Group raw data using date ranges: less than 5 days, between 5 and 14 days and o

    Hi,

    Have you considered adding a helper column to your data which identifies which group a record belongs to and then use a Pivot Table to summarise
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    07-01-2014
    Location
    Mexico
    MS-Off Ver
    2013
    Posts
    5

    Re: How to Group raw data using date ranges: less than 5 days, between 5 and 14 days and o

    Thank you Richard,
    Honestly I don't know how to create a helper column, could you please provide some guidance? once I have that value how can it be used to obtain the reults shown in the attached file?
    In the attached file there is a sheet for Raw data and another with the deired results, currently, I populate raw data, then manually select the rows within 5 days and paste them in the <5 days section in the "classified" Sheet.

    Thanks again for your help.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to Group raw data using date ranges: less than 5 days, between 5 and 14 days and o

    Hi,

    You'll need to tell us which is the relevant base date for comparing with the Open date. Is it today's date or some other date?

  5. #5
    Registered User
    Join Date
    07-01-2014
    Location
    Mexico
    MS-Off Ver
    2013
    Posts
    5

    Re: How to Group raw data using date ranges: less than 5 days, between 5 and 14 days and o

    Uppss sorry , Yes it is TODAY

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to Group raw data using date ranges: less than 5 days, between 5 and 14 days and o

    Maybe a solution like this will help you. All of your date and times are TEXT and I converted the ones needed in a helper column to real dates. I ignored the time values. I then subtracted those dates from TODAY and finally grouped the day differences into groups with an IF formula.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  7. #7
    Registered User
    Join Date
    07-01-2014
    Location
    Mexico
    MS-Off Ver
    2013
    Posts
    5

    Re: How to Group raw data using date ranges: less than 5 days, between 5 and 14 days and o

    Awesome Ron!!
    That’s and excellent solution!, thank you very much for your help! I already classified today's report using the helper column and everything looks great after some minor adjustments!!

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to Group raw data using date ranges: less than 5 days, between 5 and 14 days and o

    Thank you for the feedback and the rep.

    I'm glad that it is working with your reports.

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How to Group raw data using date ranges: less than 5 days, between 5 and 14 days and o

    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. Replies: 8
    Last Post: 07-13-2012, 09:02 AM
  2. Replies: 6
    Last Post: 03-06-2012, 03:58 PM
  3. tying to code as msgbox if date is older than 30 days
    By Zygoid in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-19-2007, 10:20 AM
  4. I want any date 90 days or older from current date change color
    By Big Abalone in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-23-2006, 12:10 AM
  5. 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

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