Count if the the critera is met then half it

[Joel]

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"))


--

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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)

 

[Bob Phillips]

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]

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