HELP! Want to create an Excel spreadsheet showing Staff Holidays

lisa76

Dear All,
I hope there's someone out there that can help me with a littel project I've set myself.

I've just taken off our staff leave cards. Currently every member of staff has their own card and I have a duplicate. I want to record the leave electronically as writing is just a waste of time!

I have basically copied the card into Excel and recording things this way, but I think this is time consuming and I'm not really interested in having an individual worksheet for each employee.

What I would like to do is have a front page which links from individual worksheets (I have 6 counties to look after so need separate sheets for them), and on this sheet will show each persons cumulative total and balance for the year - this is OK, I can sort this bit out myself.

What I would like help with is each individual sheet. Say I have 10 people per county. Someone has 2 weeks off in August - the 21st to the 1 Sept. Now there are 2 weekends and a bank holiday in here that need to be taken into account. I have tried a WORKDAY formula, but personally am finding that too difficult to use. Is there any way you can put a calendar in so I could tick days off or could I make an AUGUST column and enter 2 dates and somehow Excel will know bank hols etc.

Sorry to go on, but this has been bugging me for ages. I know what I want to do, it's just getting Excel to do it.

Cheers for anyone's input

Lisa

maforduk

Why don't you just deduct the amount of day's off their allowance so that it leaves the remainder of what's left.
e.g. say they have 25 day's annual leave a year.
Jo Bloggs takes two weeks off. (2 day's are bank holidays)
25 - 8 = 17 remaining.
On the 8 day's box, enter a Comment stating that he took two weeks off 2 day's were bank holidays.
It looks like your making this too complicated!
Sent me a PM with your e-mail address and sent me a copy of what you have, I will then e-mail it back to you completed.

Astaroth

The one we used to have was very basic - mon-friday dates only listed. BH pre populated for everyone then entered either H or 1/2H (for holiday or 1/2 day holiday) for each date the employee had off and then to sum just have a lookup the employee and a countif for the number of H, again for the 1/2H and a condition on the date so we could say how many they have had, how many are booked but not taken and how many left.

maforduk

Done the spreadsheet for you and i'v mailed it back to you.
Let me know how it is.
I have set up all the pages for printing in Portrait aswell.
Thanks.

Chippy_Minton

lisa76 wrote:

What I would like help with is each individual sheet. Say I have 10 people per county. Someone has 2 weeks off in August - the 21st to the 1 Sept. Now there are 2 weekends and a bank holiday in here that need to be taken into account. I have tried a WORKDAY formula, but personally am finding that too difficult to use. Is there any way you can put a calendar in so I could tick days off or could I make an AUGUST column and enter 2 dates and somehow Excel will know bank hols etc.

Use the NETWORKDAYS function instead:

NETWORKDAYS(start_date,end_date,holidays)

It handles weekends automatically; holidays is a range of cells containing dates to exclude, e.g. bank holidays for 2006:

New Year's Day 02-Jan
Good Friday 14-Apr
Easter Monday 17-Apr
Early May Bank Holiday 01-May
Spring Bank Holiday 29-May
Summer Bank Holiday 28-Aug
Christmas Day 25-Dec
Boxing Day 26-Dec

Given your start and end dates, the function correctly calculates 9 days.