Count if the the critera is met then half it
 

Can anyone please help with this problem.

I have a spreadsheet that notes everyones holidays, bank holidays and sick
days. It looks like this in colum:

(A2:B2) it has the name of the employee colum
(D2) Total no of holidays
(E2) Holidays Taken
(F2) Holidays Left
(G2) Total no of bank holidays
(H2) Bank Holidays Taken
(I2) Bank Holidays Left
(M4) Date (Sun 02/04/06)
(N4) Date (Mon 03/04/06) the next cell as ther following date and so on

The year is split in to two lots of 6 months because the spreadsheet doesn't
have enough colums

(A19:B19) it has the name of the employee colum
(D19) Total no of holidays
(E19) Holidays Taken
(F19) Holidays Left
(G19) Total no of bank holidays
(H19) Bank Holidays Taken
(I19) Bank Holidays Left
(M21) Date (Sun 01/10/06)
(N21) Date (Mon 01/10/06) the next cell as ther following date and so on

It works works like You have the name "Fred Bloggs" who as for example 20
days holiday, no days taken and 20 left

If a day has a 'H' int it it counts it as one holiday the same witth the
days if the have a 'BH' in or a 'S' in, it then keeps a running tottal in a
colum for me.

The problem I have is I now wish it to cou'H/2 vas have a day and not a
whole day same for 'S' and 'BH'. I amm just very unsure on how to enter it in
the formula window.

The code I have at the moment is as follows for holiday for fred bloggs is
as follows

=COUNTIF(M6:GL6,"h")+(COUNTIF(M23:GL23,"h"))


--

Count if the the critera is met then half it
 

Joel,

I use a different technique. I use H for a full-day, h for a half-day, and
use this formula

=SUMPRODUCT(--(ISNUMBER(FIND(LOWER("H"),M6:GL6)))/2+
ISNUMBER(FIND(UPPER("H"),M6:GL6)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Joel" wrote in message
...
Can anyone please help with this problem.

I have a spreadsheet that notes everyones holidays, bank holidays and sick
days. It looks like this in colum:

(A2:B2) it has the name of the employee colum
(D2) Total no of holidays
(E2) Holidays Taken
(F2) Holidays Left
(G2) Total no of bank holidays
(H2) Bank Holidays Taken
(I2) Bank Holidays Left
(M4) Date (Sun 02/04/06)
(N4) Date (Mon 03/04/06) the next cell as ther following date and so on

The year is split in to two lots of 6 months because the spreadsheet
doesn't
have enough colums

(A19:B19) it has the name of the employee colum
(D19) Total no of holidays
(E19) Holidays Taken
(F19) Holidays Left
(G19) Total no of bank holidays
(H19) Bank Holidays Taken
(I19) Bank Holidays Left
(M21) Date (Sun 01/10/06)
(N21) Date (Mon 01/10/06) the next cell as ther following date and so on

It works works like You have the name "Fred Bloggs" who as for example 20
days holiday, no days taken and 20 left

If a day has a 'H' int it it counts it as one holiday the same witth the
days if the have a 'BH' in or a 'S' in, it then keeps a running tottal in
a
colum for me.

The problem I have is I now wish it to cou'H/2 vas have a day and not a
whole day same for 'S' and 'BH'. I amm just very unsure on how to enter it
in
the formula window.

The code I have at the moment is as follows for holiday for fred bloggs
is
as follows

=COUNTIF(M6:GL6,"h")+(COUNTIF(M23:GL23,"h"))


--

Count if the the critera is met then half it
 

Than you bob for that what I am after is when it is H/2 or half I need it to
count it as 0.5


Thanks Joel
--
N/A


"Joel" wrote:

Can anyone please help with this problem.

I have a spreadsheet that notes everyones holidays, bank holidays and sick
days. It looks like this in colum:

(A2:B2) it has the name of the employee colum
(D2) Total no of holidays
(E2) Holidays Taken
(F2) Holidays Left
(G2) Total no of bank holidays
(H2) Bank Holidays Taken
(I2) Bank Holidays Left
(M4) Date (Sun 02/04/06)
(N4) Date (Mon 03/04/06) the next cell as ther following date and so on

The year is split in to two lots of 6 months because the spreadsheet doesn't
have enough colums

(A19:B19) it has the name of the employee colum
(D19) Total no of holidays
(E19) Holidays Taken
(F19) Holidays Left
(G19) Total no of bank holidays
(H19) Bank Holidays Taken
(I19) Bank Holidays Left
(M21) Date (Sun 01/10/06)
(N21) Date (Mon 01/10/06) the next cell as ther following date and so on

It works works like You have the name "Fred Bloggs" who as for example 20
days holiday, no days taken and 20 left

If a day has a 'H' int it it counts it as one holiday the same witth the
days if the have a 'BH' in or a 'S' in, it then keeps a running tottal in a
colum for me.

The problem I have is I now wish it to cou'H/2 vas have a day and not a
whole day same for 'S' and 'BH'. I amm just very unsure on how to enter it in
the formula window.

The code I have at the moment is as follows for holiday for fred bloggs is
as follows

=COUNTIF(M6:GL6,"h")+(COUNTIF(M23:GL23,"h"))


--

Count if the the critera is met then half it
 

My technique counts h as 0.5 and H as 1. A different approach.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Joel" wrote in message
...
Than you bob for that what I am after is when it is H/2 or half I need it
to
count it as 0.5


Thanks Joel
--
N/A


"Joel" wrote:

Can anyone please help with this problem.

I have a spreadsheet that notes everyones holidays, bank holidays and
sick
days. It looks like this in colum:

(A2:B2) it has the name of the employee colum
(D2) Total no of holidays
(E2) Holidays Taken
(F2) Holidays Left
(G2) Total no of bank holidays
(H2) Bank Holidays Taken
(I2) Bank Holidays Left
(M4) Date (Sun 02/04/06)
(N4) Date (Mon 03/04/06) the next cell as ther following date and so on

The year is split in to two lots of 6 months because the spreadsheet
doesn't
have enough colums

(A19:B19) it has the name of the employee colum
(D19) Total no of holidays
(E19) Holidays Taken
(F19) Holidays Left
(G19) Total no of bank holidays
(H19) Bank Holidays Taken
(I19) Bank Holidays Left
(M21) Date (Sun 01/10/06)
(N21) Date (Mon 01/10/06) the next cell as ther following date and so on

It works works like You have the name "Fred Bloggs" who as for example
20
days holiday, no days taken and 20 left

If a day has a 'H' int it it counts it as one holiday the same witth
the
days if the have a 'BH' in or a 'S' in, it then keeps a running tottal
in a
colum for me.

The problem I have is I now wish it to cou'H/2 vas have a day and not a
whole day same for 'S' and 'BH'. I amm just very unsure on how to enter
it in
the formula window.

The code I have at the moment is as follows for holiday for fred bloggs
is
as follows

=COUNTIF(M6:GL6,"h")+(COUNTIF(M23:GL23,"h"))


--

Count if the the critera is met then half it
 

If you don't like my suggestion,. I suppose that you could use

=SUMPRODUCT(-(ISNUMBER(FIND("H/2",M6:GL6)))/2+
ISNUMBER(FIND("H",M6:GL6)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Joel" wrote in message
...
Than you bob for that what I am after is when it is H/2 or half I need it
to
count it as 0.5


Thanks Joel
--
N/A


"Joel" wrote:

Can anyone please help with this problem.

I have a spreadsheet that notes everyones holidays, bank holidays and
sick
days. It looks like this in colum:

(A2:B2) it has the name of the employee colum
(D2) Total no of holidays
(E2) Holidays Taken
(F2) Holidays Left
(G2) Total no of bank holidays
(H2) Bank Holidays Taken
(I2) Bank Holidays Left
(M4) Date (Sun 02/04/06)
(N4) Date (Mon 03/04/06) the next cell as ther following date and so on

The year is split in to two lots of 6 months because the spreadsheet
doesn't
have enough colums

(A19:B19) it has the name of the employee colum
(D19) Total no of holidays
(E19) Holidays Taken
(F19) Holidays Left
(G19) Total no of bank holidays
(H19) Bank Holidays Taken
(I19) Bank Holidays Left
(M21) Date (Sun 01/10/06)
(N21) Date (Mon 01/10/06) the next cell as ther following date and so on

It works works like You have the name "Fred Bloggs" who as for example
20
days holiday, no days taken and 20 left

If a day has a 'H' int it it counts it as one holiday the same witth
the
days if the have a 'BH' in or a 'S' in, it then keeps a running tottal
in a
colum for me.

The problem I have is I now wish it to cou'H/2 vas have a day and not a
whole day same for 'S' and 'BH'. I amm just very unsure on how to enter
it in
the formula window.

The code I have at the moment is as follows for holiday for fred bloggs
is
as follows

=COUNTIF(M6:GL6,"h")+(COUNTIF(M23:GL23,"h"))


--

Count if the the critera is met then half it
 

Thanks Bob

Can I ask how do tou wrap a date for example I have got Mon 09/01/06 If i
reduce the cell in with it goes funny?

Joel
--
N/A


"Joel" wrote:

Than you bob for that what I am after is when it is H/2 or half I need it to
count it as 0.5


Thanks Joel
--
N/A


"Joel" wrote:

Can anyone please help with this problem.

I have a spreadsheet that notes everyones holidays, bank holidays and sick
days. It looks like this in colum:

(A2:B2) it has the name of the employee colum
(D2) Total no of holidays
(E2) Holidays Taken
(F2) Holidays Left
(G2) Total no of bank holidays
(H2) Bank Holidays Taken
(I2) Bank Holidays Left
(M4) Date (Sun 02/04/06)
(N4) Date (Mon 03/04/06) the next cell as ther following date and so on

The year is split in to two lots of 6 months because the spreadsheet doesn't
have enough colums

(A19:B19) it has the name of the employee colum
(D19) Total no of holidays
(E19) Holidays Taken
(F19) Holidays Left
(G19) Total no of bank holidays
(H19) Bank Holidays Taken
(I19) Bank Holidays Left
(M21) Date (Sun 01/10/06)
(N21) Date (Mon 01/10/06) the next cell as ther following date and so on

It works works like You have the name "Fred Bloggs" who as for example 20
days holiday, no days taken and 20 left

If a day has a 'H' int it it counts it as one holiday the same witth the
days if the have a 'BH' in or a 'S' in, it then keeps a running tottal in a
colum for me.

The problem I have is I now wish it to cou'H/2 vas have a day and not a
whole day same for 'S' and 'BH'. I amm just very unsure on how to enter it in
the formula window.

The code I have at the moment is as follows for holiday for fred bloggs is
as follows

=COUNTIF(M6:GL6,"h")+(COUNTIF(M23:GL23,"h"))


--