+ Reply to Thread
Results 1 to 9 of 9

Analyze rows of dates, find continuous dates and output 1 date range of min and max

  1. 02-26-2009, 04:07 PM #1
    dinged
    dinged is offline
    Registered User
    Join Date
    02-26-2009
    Location
    Orange, Ca
    MS-Off Ver
    Excel 2007
    Posts
    4

    Analyze rows of dates, find continuous dates and output 1 date range of min and max

    I'm doing an analysis on medications administration on patients. I have data entries in the thousands and I need to figure out a way to automate and summarize continuous or overlapping date ranges out of several dates and output 1 date range with a beginning date and ending date.

    Here's an Example of some dates associated with just one type of drug:
    Start Date End Date
    9/4/2008 9/11/2008
    9/11/2008 9/24/2008
    9/25/2008 10/16/2008
    10/16/2008 10/30/2008
    10/23/2008 12/6/2008
    10/31/2008 11/7/2008
    11/7/2008 11/19/2008
    11/19/2008 12/11/2008
    12/7/2008 12/7/2008
    12/7/2008 12/18/2008
    12/12/2008 12/12/2008
    12/12/2008 12/18/2008

    dates above should be analyzed and since the rows overlap or are continuous with one another I'd like an output of

    9/4/2008 12/18/2008

    For a thorough overview of what I'm trying to accomplish, I've attached an example picture of what I've been doing manually in excel for several drugs and the date ranges along with them. For the example picture I've provided, I've manually analyzed the data for 2 drugs with several date entries for each. My summary has come up with 7 date ranges of continuous medication usage.

    Any help would be greatly appreciated. Thanks!
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  2. 02-26-2009, 04:54 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Analyze rows of dates, find continuous dates and output 1 date range of min and m

    Assuming your are starting in row 2 Try this formula in E2:

    Please Login or Register  to view this content.
    Which you have to confirm with CTRL+SHIFT+ENTER not just ENTER... you will see {} brackets appear..

    And in F2:

    Please Login or Register  to view this content.
    ENTER only

    then copy both down the list...
    Last edited by NBVC; 02-26-2009 at 05:21 PM. Reason: Revised first formula to consider last date entry in range....
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 02-26-2009, 04:58 PM #3
    GuruWannaB
    GuruWannaB is offline
    Forum Contributor GuruWannaB's Avatar
    Join Date
    01-24-2008
    Location
    An hour due East of Cowtown Ohio
    MS-Off Ver
    2010
    Posts
    421

    Re: Analyze rows of dates, find continuous dates and output 1 date range of min and m

    Start Date (Change the range as needed)

    Please Login or Register  to view this content.
    End Date

    Please Login or Register  to view this content.
    Question however...as you could make this even easier. What is your comparison criteria - since in your example the dates where overlapping. Are you wanting Start and End dates of therapy of like medication names, like NDC, like GCN, or by patient? You could use this other criteria to do an automatic lookup for the criteria and then provide the start and end dates accordingly. - I am assuming there is another criteria...and probably hidden in column A on the screenshot.
    Last edited by GuruWannaB; 02-26-2009 at 05:02 PM.
    Reply With QuoteRegister To Reply
  4. 02-27-2009, 03:20 PM #4
    dinged
    dinged is offline
    Registered User
    Join Date
    02-26-2009
    Location
    Orange, Ca
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Analyze rows of dates, find continuous dates and output 1 date range of min and m

    Wow...excuse me for my amazement but I have never done in-depth excel processing and didn't now how much help this forum could be. You guys are great.

    The formula almost worked...i think....

    Thanks NBVC for the suggestion - I believe your formula is more inline with what I am looking for rather than a simple min,max formula. I tried your formula adapted to my spreadsheet and I am having trouble.

    I've attached an actual sample of the data I'm working with (patient IDs changed of course) so that maybe you can review and make suggestions based on your expertise.

    Thanks again for the help....
    Last edited by dinged; 02-27-2009 at 04:42 PM.
    Reply With QuoteRegister To Reply
  5. 02-27-2009, 03:50 PM #5
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Analyze rows of dates, find continuous dates and output 1 date range of min and m

    Does this fix it?

    If not, highlight what is wrong and tell me why.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 02-27-2009, 04:44 PM #6
    dinged
    dinged is offline
    Registered User
    Join Date
    02-26-2009
    Location
    Orange, Ca
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Analyze rows of dates, find continuous dates and output 1 date range of min and m

    Almost there. Either way, if a perfect solution can't come about, you have saved me a lot of time still....thanks!!!!

    Here are the few problems I'm seeing. I highlighted the areas in red that are a problem...

    Row 4 not calculating for the drug prednisone
    Row 7 start date is picking up "prednisone" instead of "vancomycin"
    Row 32 should have the final dates, not row 31 (I think the sort order of the dates are causing the error and I'm not sure if these types of exceptions can be accounted for)
    Row 33 would be it's own drug and need its own entry
    Row 45 need an entry for the drug "Colistimethate Inj"
    Row 47 start date is picking up "Colistimethate Inj" instead of "daptomycin"

    Also, my sort order for the work sheet is explicit by column a, then by B, C, D.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 02-27-2009, 04:58 PM #7
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Analyze rows of dates, find continuous dates and output 1 date range of min and m

    Ok, I think I understand... I will work on something... can't promise finished today, though.

    One question... Should there be dates in Row 31 and Row 32, because the date in C2 is 2 days after date in D31 as well as Row 32 being the last date for that particular drug? Or do I have that logic wrong?

    Essentially, my logic was, if the date in column C after the current row is more than 1 day later than the date in column D of the current row, then I need to get the start/end dates for ending the date in the current row. Is that correct?
    Reply With QuoteRegister To Reply
  8. 02-27-2009, 05:07 PM #8
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Analyze rows of dates, find continuous dates and output 1 date range of min and m

    If my logic is correct, then I think the attached does the trick...

    All the red cells are now filled in....

    Let me know what you think.

    Changed formula in E2 to:

    Please Login or Register  to view this content.
    and confirmed with CTRL+SHIFT+ENTER and copied down.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 02-27-2009, 06:06 PM #9
    dinged
    dinged is offline
    Registered User
    Join Date
    02-26-2009
    Location
    Orange, Ca
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Analyze rows of dates, find continuous dates and output 1 date range of min and m

    You are a genius. Thank you so much!!! I've applied it to the larger data set and so far things look really good. I'm going to have some of my students do some additional eyeball work (since the data is so large) to make sure the data is correctly applied to the entire data set and let you know how things are going.

    You've just saved our team probably weeks of work. Thank you!!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1