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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

s-o-s

Active Member
Joined
Apr 14, 2002
Messages
384
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?
 

OWAIZ

Board Regular
Joined
Jun 20, 2002
Messages
89
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
 

OWAIZ

Board Regular
Joined
Jun 20, 2002
Messages
89
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
 

OWAIZ

Board Regular
Joined
Jun 20, 2002
Messages
89
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
 

s-o-s

Active Member
Joined
Apr 14, 2002
Messages
384
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.
 

OWAIZ

Board Regular
Joined
Jun 20, 2002
Messages
89
Thanks for ur kind help SOS,

THanks and regards
Owaiz
 

s-o-s

Active Member
Joined
Apr 14, 2002
Messages
384
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
2JoeBloggsPE0001243
27
3BartSimpsonPT0001120
12
4LisaSimpsonPE0002246
30
5
0
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
 

s-o-s

Active Member
Joined
Apr 14, 2002
Messages
384
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
2JoeBloggs
PE0001
27
01/01/0202/01/020
2
3BartSimpson
PT0001
12
03/01/0204/01/020
2
4LisaSimpson
PE0002
30
01/01/0201/01/020
1
5LisaSimpson
PE0002
30
04/01/0204/01/020
1
6
 
 
0
0
Input
 

s-o-s

Active Member
Joined
Apr 14, 2002
Messages
384
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,626
Messages
5,469,785
Members
406,670
Latest member
Jimborusk13

This Week's Hot Topics

Top