+ Reply to Thread
Results 1 to 3 of 3

Find the start and end date of range of consecutive dates.

  1. #1
    Registered User
    Join Date
    03-25-2013
    Location
    Houston, Tx
    MS-Off Ver
    Excel 2010
    Posts
    2

    Find the start and end date of range of consecutive dates.

    I have a column of data (formatted as a date). It stands for the days of the year for which I am missing information. I need a formula that will read through that column and copy the start and end dates for all the different date ranges within that column, onto two new columns. I've attached an image of the workbook with the column that I currently have (Column A) and what I would like my end results to be (Columns B and D). Thanks in advance.

    Sample workbook.png

  2. #2
    Forum Moderator 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
    26,797

    Re: Find the start and end date of range of consecutive dates.

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

    Please don't upload pictures. None of us are inclined to recreate your data when you have a workbook available - see guidelines in the Rules area of this forum.
    Richard Buttrey

    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
    03-25-2013
    Location
    Houston, Tx
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Find the start and end date of range of consecutive dates.

    My apologies! I did not see the attachment icon. I am new and still trying to figure this out. I've attached a sample workbook now. Column A includes the data that I have, and Columns B and D is what I am trying to get through an equation or macro. I manually entered each cell in Columns B and D in the sample workbook, but I need a way to automate this process since I have about 46 workbooks with dates from January-December.

    The context behind this data: I am working on a traffic study for a US Interstate Highway. We were given hourly counts of vehicles for various sites along this highway for 24 hours for the entire year of 2012. However, we are missing information for various dates of the year. Sometimes it is a range of dates, others it is a single day. But what I would like to do is separate the missing dates into a range so I can have a more condensed list for me to give to the data collectors. This way they know what range of dates they need to send us data for.


    Sample Workbook.xlsx

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1