Holiday Tracker - Summary Sheet - Help Needed.

[Liquidity]

Hi Everyone!!

Awesome forum. I'm hoping to learn lots about Excel!

Anyhoo, just recently had a new boss at work who has decided that he doesn't like all the work he supposed to do, so now I'm to do it all

So I've been given the delightful task of dealing with the holidays in the depot, including allocating and tracking them to make sure our employees do ot take too many. (This year we have people who are 15 days over their entitlement...Eeeek)

So I've put together a workbook with 52 sheets, one per week. Each sheet is identical and is split into 3 sections. First section records full weeks of annual leave (5 days) and is split into 2 as we have morning and late shifts. We allow a max of 8 per shift to have full weeks per week.

Second section is for those only wanting odd days. Third section totals everything and gives me number off and total as a %.

What I could do with s a check sheet that shows my full staff list (160 odd) and searches all the sheets and totals up the number of holidays each employee has taken in total. So full weeks as well as odd days.

I need this to be pretty flexible as we do have a reasonable turnover of staff, so I need to add and delete them as required.

Can anyone help me with functions or formula's that could hep me?

Much appreciated in advance.

Cheers

Liquidity

[Liquidity]

Demo attached to help with explanation.

Cheers

Liquidity

[JohnTopley]

It would be helpful if you can post a small sample file with examples of the data and calculations (showing results).

Beat me to it but it still help if you added expected results.

What is annual holiday entitlement?

Is a week 5 days holiday?

Do we need to use calculation on Lieu Days?

[Liquidity]

Hi John,

I've just posted a small demo of the proper workbook and marked the section I need help with. It's just above your post.

Hope this helps.

Thanks

Andy

[JohnTopley]

Andy,
can you add expected results so we can check any calculations.

Thank you.

[AliGW]

Andy - you need to add what you expect the calculation to do and some expected results.

[Liquidity]

Hi guys,

Yes. Makes sense.

So I've (hopefully) attached another demo with the results I'd like to get automatically.

So from the three sheets you can see ME has had 2 weeks off (10 days). D has had one full week and 2 days the following week (7day) etc.

I hope this makes sense!