leave planner

**tony0710** · 11-05-2009, 07:53 PM

Hi all

I really liked the annual holiday found in:

http://www.excelforum.com/excel-gene...e-planner.html

I would like some tweaks done to it:

Start the planner from 1st april to 31st march
Have another list of all bank holidays in the year, and excel to automatically insert the relevant square with the letters "BH".
I've got staff that work tuesday to saturday - so the grey squares will be for sunday and monday
I've another staff that works mon, tues, thur, fri, sat - so her grey squares will be wed and sun
I would like to add a description to the dates - column D - to show reason of day off and to format the squares accordingly. So if I input dates, and wrote "paid holiday" in column D: the relevant squares will go green with "PH" letters. Then there could be another instance in which I could write "unpaid sick" in column D - I would like that to show in a different coloured square with "US" letters. Again for "Paid Sick" - PS; "Unpaid Holiday" - UH, "Overtime" - then have a pop up input box to ask how many hours and to have the number of hours and the letter O in the square, e.g. lady done 4 hours overtime - to have a coloured square showing "4O". Once done to show a summary of each type of activity for each person.

Any help will be helpful - I'll try to resolve some of these myself - but would appreciate a pointer.

Regards

Tony

**tony0710** · 11-07-2009, 06:39 AM

Am I asking for too much

??

**martindwilson** · 11-07-2009, 07:05 AM

the easy bit
Start the planner from 1st april to 31st march
just put 01/04/09 in cell q1
then tidy up the formating(cell borders font of last days of months affected)
for non working days you can change the formula to
=SUMPRODUCT(($E5=SNames)*(SFrom<=F$4)*(STo>=F$4))+IF(OR(WEEKDAY(F$4,2)={6,7}),2,0) sat/sun
=SUMPRODUCT(($E5=SNames)*(SFrom<=F$4)*(STo>=F$4))+IF(OR(WEEKDAY(F$4,2)={1,7}),2,0) mon/sun
=SUMPRODUCT(($E7=SNames)*(SFrom<=F$4)*(STo>=F$4))+IF(OR(WEEKDAY(F$4,2)={3,7}),2,0) wed/sun

**tony0710** · 11-11-2009, 04:19 AM

Thanks

I've got:

Staff AC, BY, LB, GS - working Tu,We,Th,Fr,Sa
Staff JW - working Mo,Tu,Th,Fr,Sa
Staff EC - working We,Fr
Staff NM - working Mo,Tu,We,Th,Fr

So in the formulas that you suggested above - perhaps one can replace "SNames" with the relevant staff above?

**martindwilson** · 11-11-2009, 05:23 AM

snames is a named range a4:a57 where the names are listed when leave booked
youd need a formula as above for each of those groupings against each name that works to that pattern

**tony0710** · 11-11-2009, 11:08 AM

Hi Martin

Thanks - I've used your formulas in the main body of the planner and all appears to be working well, so far.

Still got a long way to go, but the basic planner is shaped well.

Any suggestions on some of the other issues that I posted in my original posted?

Regards

Tony

**martindwilson** · 11-11-2009, 11:43 AM

afraid not i had alook but it would require a total rework

**tony0710** · 11-11-2009, 12:52 PM

No Way! That's a big blow!!

As that was the main reason for my planner:

Keep track of holidays (paid, unpaid)
Track days off sick (paid, unpaid)
Track overtime done
Bank holiday interaction with holidays - as the holiday entitlement is inclusive of bank holidays

A major blow!! Will have to sit down and re-think the whole project.