VACATION PLANNING

OWAIZ

Board Regular
Joined
Jun 20, 2002
Messages
89
HI Champs,
I have working on a project "vacation plan" in excel.. i want a small help. i will be entering all the details of the leave for eg. 1)name 2)employee code 3)no. of days eligible for leave 4)starting day & date of the leave 5) end day and date of the leave 6) no. of today days. on sheet1.
on sheet 2. i will have a calender for the entire year. i want this calender to get updated as soon as i put in the date in sheet1 with the names of the agents in the calender & if the leaves overlap, it should change the colour with a conditional formatting. i understand it's complicated. but with the help of u champs, i am dead sure that i will make. it. as i have done it earlier on many complicated projects.

thanks for ur help in advance

thanks & regards
owaiz
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi OWAIZ,

Just to confirm, do you want Sheet1 to be a database that lists down, each line being one specific holiday period?

Sheet 2 then summarises all entries relating to one individual?
 
Upvote 0
hI sos,
I will have the data base for all the employee on sheet one and the calender on sheet 2. the inputs will be in sheet 1 and the output i want it on sheet2 in a calender form.. showing there names each day they will be on leave..

if u have an example or sample file to share with me pls forward me.
will appreciate ur help.

thanks & regards
owaiz
 
Upvote 0
hI sos,
I will have the data base for all the employee on sheet one and the calender on sheet 2. the inputs will be in sheet 1 and the output i want it on sheet2 in a calender form.. showing there names each day they will be on leave..

if u have an example or sample file to share with me pls forward me.
will appreciate ur help.

thanks & regards
owaiz
 
Upvote 0
hI sos,
I will have the data base for all the employee on sheet one and the calender on sheet 2. the inputs will be in sheet 1 and the output i want it on sheet2 in a calender form.. showing there names each day they will be on leave..

if u have an example or sample file to share with me pls forward me.
will appreciate ur help.

thanks & regards
owaiz
 
Upvote 0
On 2002-07-29 04:41, OWAIZ wrote:
hI sos,
I will have the data base for all the employee on sheet one and the calender on sheet 2. the inputs will be in sheet 1 and the output i want it on sheet2 in a calender form.. showing there names each day they will be on leave..

if u have an example or sample file to share with me pls forward me.
will appreciate ur help.

thanks & regards
owaiz

I am working on a solution...I have done something similar...creating a timeplan(for projects I will be working on and when vs actuals - a bit like MS Project I guess)...and trying to adapt this...

Problem 1 It has to be rotated through 90 degrees as you are not able to get whole calendar onto the sheet due to 256 column limit, 52*5 working days = 260 days therefore columns have just become rows...

I'm started off using Data validation to limit the input to valid employees and then using that name to perform a lookup for Employee code, Days available etc, this is kept in a seperate database with Annual entitlement plus days c/fwd to give this years entitlement.

If anyone has already worked this please join in...

If not I'll keep working on it.
 
Upvote 0
On 2002-07-29 05:18, OWAIZ wrote:
Thanks for ur kind help SOS,

THanks and regards
Owaiz

Had to delete the last post something went very wrong...I think I hit a limit in posting for length of post using 3 of Colo's/ Ivans extremely useful tools....

Therefore this post will be in three parts...

Hi,

The 1st database consists of valid employee names, of which the 1st column is a named range "Emp_Names", the whole database is named "Employees".

Here is the 1st sheet.
VacPlan.xls
ABCDE
1NameEmployeeCodeAnnualEntitlementB/FwdThisYear
2JoeBloggsPE000124327
3BartSimpsonPT000112012
4LisaSimpsonPE000224630
50
Employees



The Employees range is used to populate fields on both the other sheets via lookup tables.

The next part follows shortly....
This message was edited by s-o-s on 2002-07-29 06:40
 
Upvote 0
The next part of this uses the NetWorkDays function which requires the Analysis Toolpack Addin...if not already installed you may need to get hold of your MS office setup disks.

Here is the Input sheet...
VacPlan.xls
ABCDEFG
1NameEmployeeCodeAvailableStartDateEndDateHolidaysNetworkDays
2JoeBloggsPE00012701/01/0202/01/0202
3BartSimpsonPT00011203/01/0204/01/0202
4LisaSimpsonPE00023001/01/0201/01/0201
5LisaSimpsonPE00023004/01/0204/01/0201
6  00
Input
 
Upvote 0
The last part uses the Sumproduct function to populate the presence or absence of a holiday on any given day. I will show the conditional formatting formula being used below.

Format| Conditional Formatting
Change drop down to formula is
Enter...

=AND(SUM($B6:$K6)>1,B6>0)

copy the formatting to all cells used on the calendar then it will work.

I set the background to red, now whenever 2 or more people are on vacation on the same day the cells of the relevant people show up as Red.

If you would like I can email this through to you...drop me a PM or email...

PS About to finish lunch so will not pick up any messages until tonight.

Hope this helps.
 
Upvote 0

Forum statistics

Threads
1,224,521
Messages
6,179,280
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top