+ Reply to Thread
Results 1 to 12 of 12

Formula to display sequence number based on contiguous start and end dates

  1. #1
    Registered User
    Join Date
    11-01-2013
    Location
    Scotland
    MS-Off Ver
    Excel 2003
    Posts
    6

    Thumbs up Formula to display sequence number based on contiguous start and end dates

    Hi

    My first post so please be gentle with me.

    I'm trying to create a formula that will create a sequence number where there are 3 columns as follows: ID, Start Date and End Date. The data looks like:

    ID START END
    7654321 01-Feb-13 05-Feb-13
    1234566 01-Jul-13 07-Jul-13
    1234566 08-Jul-13 12-Jul-13
    1234567 01-Jun-13 07-Jun-13
    1234567 08-Jun-13 15-Jun-13
    1234567 16-Jun-13 24-Jun-13
    1234567 25-Jun-13 30-Jun-13
    1234567 01-Sep-13 07-Sep-13
    1234567 08-Sep-13 17-Sep-13
    1234568 01-May-13 02-May-13

    So with each change in ID a new sequence number should be created. But importantly the dates have to be contiguous such that the sequence of numbers would looks something like the following:

    ID START END
    7654321 01-Feb-13 05-Feb-13 1
    1234566 01-Jul-13 07-Jul-13 2
    1234566 08-Jul-13 12-Jul-13 2
    1234567 01-Jun-13 07-Jun-13 3
    1234567 08-Jun-13 15-Jun-13 3
    1234567 16-Jun-13 24-Jun-13 3
    1234567 25-Jun-13 30-Jun-13 3
    1234567 01-Sep-13 07-Sep-13 4
    1234567 08-Sep-13 17-Sep-13 4
    1234568 01-May-13 02-May-13 5

    You will notice that the sequence number changes if there is a break in the dates even if the ID remains the same.

    These are basically a listing of staff sickness dates where absence occurrences are defined by contiguous dates.

    Really hope someone can help me out with this

    Kind regards

    Alan

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Formula to display sequence number based on contiguous start and end dates

    With your sample data in A1:C11...
    This regular formula, copied down, begins the sequence
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    11-01-2013
    Location
    Scotland
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Formula to display sequence number based on contiguous start and end dates

    Thank you so very much kind sir.

    That worked a treat!

    It had me really stumped.

    Best wishes from [not so sunny] Scotland

    Alan

  4. #4
    Registered User
    Join Date
    11-01-2013
    Location
    Scotland
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Formula to display sequence number based on contiguous start and end dates

    Hi

    Me again, spoke to soon.

    What I asked for works perfectly well with the test data, however when I run the formula against the 'real' data is doesn't work in one specific instance. That is where a contiguous sequence of dates crosses months. So for example if you had a start date as the first day of the next month it doesn't recognize that as a valid sequence following on from the end date of the previous month?

    Apologies

    Alan

  5. #5
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Formula to display sequence number based on contiguous start and end dates

    Can you post some sample data that exhibits the issue?

  6. #6
    Registered User
    Join Date
    11-01-2013
    Location
    Scotland
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Formula to display sequence number based on contiguous start and end dates

    Quote Originally Posted by Ron Coderre View Post
    Can you post some sample data that exhibits the issue?
    Thanks for taking the time to look at this I really do appreciate it:

    Staff Member Start Date End Date
    0000112 24/10/2012 31/10/2012
    0000112 01/11/2012 27/11/2012
    0000188 24/01/2013 25/01/2013
    0000188 12/08/2013 13/08/2013
    0000230 03/12/2012 07/12/2012
    0000231 29/01/2013 31/01/2013
    0000232 03/12/2012 03/12/2012
    0000233 26/11/2012 30/11/2012
    0000233 03/01/2013 06/01/2013
    0000233 07/01/2013 31/01/2013
    0000233 01/02/2013 28/02/2013
    0000233 01/03/2013 31/03/2013
    0000233 01/04/2013 30/04/2013
    0000233 01/05/2013 10/05/2013
    0000234 19/02/2013 28/02/2013
    0000234 01/03/2013 31/03/2013
    0000234 01/04/2013 30/04/2013
    0000234 01/05/2013 31/05/2013
    0000234 01/06/2013 30/06/2013
    0000234 01/07/2013 31/07/2013
    0000234 01/08/2013 18/08/2013
    0000240 11/03/2013 31/03/2013
    0000240 01/04/2013 30/04/2013
    0000240 01/05/2013 31/05/2013
    0000240 01/06/2013 30/06/2013
    0000240 01/07/2013 31/07/2013
    0000240 01/08/2013 31/08/2013
    0000240 01/09/2013 30/09/2013
    0000240 01/10/2013 31/10/2013
    0000246 14/11/2012 15/11/2012
    0000246 09/09/2013 10/09/2013
    0000247 04/01/2013 13/01/2013
    0000247 14/01/2013 31/01/2013

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

    Re: Formula to display sequence number based on contiguous start and end dates

    Thought that I had it figured out .... but didn't

    On second thought, isn't this correct?

    0000112 24/10/2012 31/10/2012 1
    0000112 01/11/2012 27/11/2012 2
    0000188 24/01/2013 25/01/2013 3
    0000188 12/08/2013 13/08/2013 4
    0000230 03/12/2012 07/12/2012 5
    0000231 29/01/2013 31/01/2013 6
    0000232 03/12/2012 03/12/2012 7
    0000233 26/11/2012 30/11/2012 8
    0000233 03/01/2013 06/01/2013 9
    0000233 07/01/2013 31/01/2013 9
    0000233 01/02/2013 28/02/2013 10
    0000233 01/03/2013 31/03/2013 11
    0000233 01/04/2013 30/04/2013 12
    0000233 01/05/2013 10/05/2013 13
    0000234 19/02/2013 28/02/2013 14
    0000234 01/03/2013 31/03/2013 15
    0000234 01/04/2013 30/04/2013 16
    0000234 01/05/2013 31/05/2013 17
    0000234 01/06/2013 30/06/2013 18
    0000234 01/07/2013 31/07/2013 19
    0000234 01/08/2013 18/08/2013 20
    0000240 11/03/2013 31/03/2013 21
    0000240 01/04/2013 30/04/2013 22
    0000240 01/05/2013 31/05/2013 23
    0000240 01/06/2013 30/06/2013 24
    0000240 01/07/2013 31/07/2013 25
    0000240 01/08/2013 31/08/2013 26
    0000240 01/09/2013 30/09/2013 27
    0000240 01/10/2013 31/10/2013 28
    0000246 14/11/2012 15/11/2012 29
    0000246 09/09/2013 10/09/2013 30
    0000247 04/01/2013 13/01/2013 31
    0000247 14/01/2013 31/01/2013 31
    Last edited by newdoverman; 11-02-2013 at 10:33 AM.
    <---------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

  8. #8
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Formula to display sequence number based on contiguous start and end dates

    Thanks for posting the sample data.

    If you want to increment the counter whenever there's a change in ID and/or the new start date is not continuation of the previous date range...try this regular formula, copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Does that help?
    (If no...please re-post the sample data AND the results you want to see.)

  9. #9
    Registered User
    Join Date
    11-01-2013
    Location
    Scotland
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Formula to display sequence number based on contiguous start and end dates

    Hi Ron

    Thanks for your continued indulgence. I want the data grouped by ID but also by continuous dates. Such that if the date range stops being continuous even within the same ID a new sequence is created. The output should looks like this:

    0000112 24/10/2012 31/10/2012 1
    0000112 01/11/2012 27/11/2012 1
    0000188 24/01/2013 25/01/2013 2
    0000188 12/08/2013 13/08/2013 3
    0000230 03/12/2012 07/12/2012 4
    0000231 29/01/2013 31/01/2013 5
    0000232 03/12/2012 03/12/2012 6
    0000233 26/11/2012 30/11/2012 7
    0000233 03/01/2013 06/01/2013 8
    0000233 07/01/2013 31/01/2013 8
    0000233 01/02/2013 28/02/2013 8
    0000233 01/03/2013 31/03/2013 8
    0000233 01/04/2013 30/04/2013 8
    0000233 01/05/2013 10/05/2013 8
    0000234 19/02/2013 28/02/2013 9
    0000234 01/03/2013 31/03/2013 9
    0000234 01/04/2013 30/04/2013 9
    0000234 01/05/2013 31/05/2013 9
    0000234 01/06/2013 30/06/2013 9
    0000234 01/07/2013 31/07/2013 9
    0000234 01/08/2013 18/08/2013 9
    0000240 11/03/2013 31/03/2013 10
    0000240 01/04/2013 30/04/2013 10
    0000240 01/05/2013 31/05/2013 10
    0000240 01/06/2013 30/06/2013 10
    0000240 01/07/2013 31/07/2013 10
    0000240 01/08/2013 31/08/2013 10
    0000240 01/09/2013 30/09/2013 10
    0000240 01/10/2013 31/10/2013 10
    0000246 14/11/2012 15/11/2012 11
    0000246 09/09/2013 10/09/2013 12
    0000247 04/01/2013 13/01/2013 13
    0000247 14/01/2013 31/01/2013 13
    0000247 01/02/2013 28/02/2013 13
    0000247 01/03/2013 31/03/2013 13
    0000247 01/04/2013 30/04/2013 13
    0000247 01/05/2013 31/05/2013 13
    0000247 01/06/2013 04/06/2013 13
    0000261 01/10/2012 29/10/2012 14
    0000261 03/12/2012 31/12/2012 15
    0000261 01/01/2013 09/01/2013 15

    Hope this makes sense

    Kind regards

    Alan

  10. #10
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Formula to display sequence number based on contiguous start and end dates

    Got it...Thanks for the additional information.
    Try this regular formula, copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Have we solved it yet?

  11. #11
    Registered User
    Join Date
    11-01-2013
    Location
    Scotland
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Formula to display sequence number based on contiguous start and end dates

    Looking very good....

    Yep all good ....

    All resolved.

    Very many thanks

    Alan

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Formula to display sequence number based on contiguous start and end dates

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ 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: 2
    Last Post: 05-19-2013, 03:59 AM
  2. VLookup Alphabetical List, then display next number in a sequence
    By ianarman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-24-2012, 04:18 AM
  3. Replies: 5
    Last Post: 11-22-2010, 06:31 PM
  4. count and display the next number in a sequence
    By chuck515 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-31-2008, 12:04 AM
  5. [SOLVED] Display zero at the begnning of a number sequence in Excel
    By Janelle Lister in forum Excel General
    Replies: 2
    Last Post: 07-27-2006, 10:55 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