Please help

[Tia]

Hi,

I am trying to find a formula that allows me to know how many days an
employee took as a vactaion on each month a summary sheet for each
employee
In order to find the number of days taken i have use the following :


B22=From (date)
C22= To Date
D22= Total Taken
C22:C44 =Official Holidays
=NETWORKDAYS(B22,C22,C44:C53)


Please advise
Tia

 

[Pete_UK]

I've seen this, and variations of it, posted in several newsgroups -
it is not a good idea to multi-post.

You give no details of where your employee names are located, so are
we meant to guess how your data is laid out?

Pete

 

[Tia]

Im sorry for posting so many times but i give up searching it seems
that im a bad formula searcher b i dont know where to look thank you
for caring

I made an excel sheet to calculate the number of the vacation taken
by
the employees,excluding the weekend and the official holidays.
every employee has his own sheet
Here is the formula that i have used to know the total of days taken
each time :
B22=From (date)

what i do really want now is to find formula that allows me to know
the
number of all the vacation requested (taken) in each month of the
year
as a summary sheet
A5=NAME
C5=JANUARY
D5=FEBRUARY
etc......

For each employee in his own sheet i wana make a summary for each
month

Thank you

 

[Pete_UK]

How would you want to treat the situation where the From date is in
March (e.g. 28th March) and the To date is in April (e.g. 6th April),
giving 6 days leave? This is actually 2 days in March and 4 days in
April, assuming current year. Are your dates inclusive?

Pete

 

[Tia]

actually u are right but i can always divide it
Example :
Vacation taken =20/03/2008 till 05/04/2008
i will enter it as

B22 From till =C22 Total days taken =D22
B23 20/3/08 31/3/2008 6
B24 1/4/2008 05/4/2008 4

 

[Tia]

U are actually right
but i could treat this situation by the following action (i will
always seperate 2 months or 2 different year in my data entry)

Example
Vacation date from 25/03/2008 till 05/04/2008
i will do the following:
B22 From C22= To D22= Total taken
25/03/2008 31/03/2008 5 days
01/04/2008 05/04/2008 4 days

 

[Pete_UK]

So in that case we can look at the From column to determine the month
in which the leave is taken?

Incidentally, are you likely to have a new workbook for another year's
leave patterns, or will you continue to use this workbook? (In which
case you will need more columns in your summary sheet or a way of
selecting the year in that sheet - which would you prefer?)

Pete

 

[Tia]

I think its better to have it as an extra column in my summary sheet
Correct me if im wrong
Ex: March 07= C5
March 08=D5
right ?

if is it so thats ok

thank u in advance

 

[Pete_UK]

Well, if C5 is Jan 08, you would want to continue across with Feb 08,
March 08 etc, so Jan 09 would be in cell O5, then Feb 09, March 09
etc.

I'll have a play about and get back to you later on.

Pete

 

[Tia]

Yes exactly

Thank you for your help
Waiting your reply
Tia

 

[Bryan De-Lara]

Tia, I did offer you the workbook I have already done for holidays, minus
week-end and bank holidays.

Bryan.
U are actually right
but i could treat this situation by the following action (i will
always seperate 2 months or 2 different year in my data entry)

Example
Vacation date from 25/03/2008 till 05/04/2008
i will do the following:
B22 From C22= To D22= Total taken
25/03/2008 31/03/2008 5 days
01/04/2008 05/04/2008 4 days

 

[Pete_UK]

Okay, I set up a sheet called "John" with "From" in B21, "To" in C21
and "Days" in D21, and then put some dates in B22:C22 with appropriate
days in D22, and repeated this on a few more rows. I copied the sheet
a few times and renamed the copies to "Mary", "Fred" etc and set up
some more test data.

In a sheet called "Summary", I put "Name" in A5, with "John", "Mary",
"Fred" etc down the column. and then in C5 I put the date 1/01/08 and
used a custom format on this cell of mmm yy to display as Jan 08. Then
I put this formula in D5:

=DATE(YEAR(C5),MONTH(C5)+1,1)

and formatted that as mmm yy so it displayed as Feb 08, and then
copied this cell across to give me sequential months. You can change
the start date in C5 to suit the period of time that your data covers.

I put this formula in C6:

=SUMPRODUCT((MONTH(INDIRECT("'"&$A6&"'!B22:B50"))=MONTH(C
$5))*(YEAR(INDIRECT("'"&$A6&"'!B22:B50"))=YEAR(C$5))*(INDIRECT("'"&
$A6&"'!D2250")))

and then copied this across and down as appropriate - this returned
the number of days leave for each person in each month, so hopefully
this is what you wanted. If you have data beyond row 50 for any one of
your employees, then you will need to amend the ranges appropriately -
there is no problem if you make them much larger, but the formulae
might take longer to calculate.

Hope this helps.

Pete