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
Bookmarks