Start and End Dates for Consecutive Dates

Hi,

I need help....I have a list of id numbers with absence dates. I need to be able to identify the start and end date of each consecutive absence where start date is column b and end date would be column c with the number of consecutive days in column d. Any single day absence would be included as well. We do not work weekends or holidays.

I have attached a list of my current data.

Please let me know if you need anymore information.

Thanks,

Melissa

So I tried running and I am not getting any data.

I don't know if this will help at all

I need it read something like this ...obviously I have done this manually

ID Start of absence End of Absence # of Consecutive days
10 1/22/2015 1/22/2015 1
10 1/29/2015 1/29/2015 1
10 2/26/2015 2/26/2015 1
10 3/11/2015 3/11/2015 1
10 4/20/2015 4/21/2015 2

I can't thank you enough for looking at this

Hi,

I apologize I thought I explained my needs in my original post but when I read above I obviously did not clarify. We are currently implemented an absence program with multiple dynamics but we do not have the resources at this time to program a lot of the reports I need run the program so I need to work with the data I currently have. Right now I can pull a list of absences. Each absence is list as an individual absence. I have a couple of needs and maybe you guys may have a better way to structure the information but I need to identify any consecutive absence and how many days occur within that absence.

As Keebellah, indicated the assumption was that the next absence in the list was the end of absence. That is not a correct assumption. The start date would the first date in the series of consecutive dates and end date would be the last date in the series consecutive dates.

I hope that clarifies. Please let me know if I need to explain more details.

I don't think Iwas clear about what I was looking for. And I am sorry about that. When I look at your program it gives me the first date in the data set as the start date then the end date as the second date in the data set. The data from your program then reads that there is 7 absences that occurred between those two dates. But when you go back to my original data there is no absences in January 23, 24...etc. What I am looking for is January 22 is the start date because the next date in the data is not a consecutive date that is the end of the occurrence. So January 22 is one occurrence with 1 day taken. The same would be for January 29, February 22 and March 11. then we get to the April 20, April 21 and April 24. April 20 and April 21 are consecutive dates so they would be a consecutive absence which is one occurrence with two days. Because April 24 is not a consecutive date it would start a new occurrence. My logic was cell b2 would be the first start date. If the ids were the same and b2 does not equal the b2+1 then it would be the end date but if b2 does = b2+1 the ignore and continue until the dates are no longer consecutive. Then I would need in the next column the number of consecutive days that occurred in the range. I just don't know how to write this especially factoring in that we do not work weekends or holidays.

I hope that makes more sense if not I can try to explain another way

Cheers

MissC,

Is this how you wanted?

Please Login or Register  to view this content.

Sorry for the delay. It's Thanksgiving weekend in Canada so it's been quite hectic.

Yes Keebellah, your assumption is correct.