Calculating total shifts worked across weekly sheets.

If anyone could help that would be great.

I would like to compile a summary page of the amount of shifts a person has worked over a given period be it a year or monthly. I have attached a copy of the sheets in question.

The summary page should look up the name in column A of a weekly sheet and return the value in Column AR and then add the value returned for each week together.

I have included a broken formula on the summary page as to what I think I may be looking for but obviously I can't figure it out. To be honest im probably barking up the wrong tree and theres a different formula that I could use. I'm by no means experienced with Excel.

=VLOOKUP(A6,'5.7.15'!A5:A8+'28.6.15'!A5:A8,COLUMN(AR),FALSE)

The weekly sheet isn't in alphabetical as they are required to be in teams and usually there are 50+ names (8 teams) and I have weekly sheets for the whole year.

I'm not even sure if it's possible. If not I will have to go through the whole sheet manually adding up each weeks shifts per person onto a monthly report.

Many thanks in advance if anyone is able to assist.

Jon.

13 consecutive days (including weekends) starting on any given day.

So if they worked Sunday 14th June through to Friday 26th June, They would have to take the Saturday 27th off. If they were to take the 25th off sick for instance, the count would start again.

Although if that's not possible, If it could work out Monday to Sunday for the last two weeks and show me a count so I could identify a breech which might occur in the planning stage after I've input the shifts for the following week. Even if it means I have to adjust the formula for each week I add.

Edit: Apologies, I've reworded the first line.

Hi John,

I have added a couple of extra bits of data and some notes. Would you be able to take a look and see if you could adapt it.

It looks like I have broken the macro adding the new people. Could you advise me as to why this occurred so I can transfer it to my actual file.

Thank you.

Edit: Solid Red Cells should have no data in them other than Red Fill. I just noticed one had a 1 in it which I forgot to remove.

Edit 2: I've included the actual sheet with personal data removed so you can see the scale im working with.

Your correct in the thinking, that is 13 consecutive days for week 1 and 2. Sorry it's my explanation. When I'm planning week 3. If a shift is missed it must start counting again from 1. Showing only the amount of shifts worked since the last day off up to the Sunday on the final week (5.7.15). (calculating to the final Sunday - Week 3 - as it should never be more than 13 shifts worked in past weeks if I've done my job right)

I'm not sure I've explained that any better.

If it's too much trouble don't worry I'll make do with what you've helped me with so far. It's already made my job a great deal easier.

Edit:

In my example, Where 28-Jun was taken off as a rest day. 14 days wasn't reached in week 1 or 2.

So moving on I need it to show the amount of days worked in weeks 2 and 3 since the last day off.

So if John Smith didn't work 20th Jun but then worked right from the 21st to the 3rd of July. That would be his 13th shift so would need the 4th off.

In a sense, not when he has worked 13 shifts but if he will.