software

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 quot;Fred Bloggsquot; 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,quot;hquot;) (COUNTIF(M23:GL23,quot;hquot;))--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(quot;Hquot;),M6:GL6)))/2
ISNUMBER(FIND(UPPER(quot;Hquot;),M6:GL6)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Joelquot; gt; wrote in message
...
gt; Can anyone please help with this problem.
gt;
gt; I have a spreadsheet that notes everyones holidays, bank holidays and sick
gt; days. It looks like this in colum:
gt;
gt; (A2:B2) it has the name of the employee colum
gt; (D2) Total no of holidays
gt; (E2) Holidays Taken
gt; (F2) Holidays Left
gt; (G2) Total no of bank holidays
gt; (H2) Bank Holidays Taken
gt; (I2) Bank Holidays Left
gt; (M4) Date (Sun 02/04/06)
gt; (N4) Date (Mon 03/04/06) the next cell as ther following date and so on
gt;
gt; The year is split in to two lots of 6 months because the spreadsheet
doesn't
gt; have enough colums
gt;
gt; (A19:B19) it has the name of the employee colum
gt; (D19) Total no of holidays
gt; (E19) Holidays Taken
gt; (F19) Holidays Left
gt; (G19) Total no of bank holidays
gt; (H19) Bank Holidays Taken
gt; (I19) Bank Holidays Left
gt; (M21) Date (Sun 01/10/06)
gt; (N21) Date (Mon 01/10/06) the next cell as ther following date and so on
gt;
gt; It works works like You have the name quot;Fred Bloggsquot; who as for example 20
gt; days holiday, no days taken and 20 left
gt;
gt; If a day has a 'H' int it it counts it as one holiday the same witth the
gt; days if the have a 'BH' in or a 'S' in, it then keeps a running tottal in
a
gt; colum for me.
gt;
gt; The problem I have is I now wish it to cou'H/2 vas have a day and not a
gt; whole day same for 'S' and 'BH'. I amm just very unsure on how to enter it
in
gt; the formula window.
gt;
gt; The code I have at the moment is as follows for holiday for fred bloggs
is
gt; as follows
gt;
gt; =COUNTIF(M6:GL6,quot;hquot;) (COUNTIF(M23:GL23,quot;hquot;))
gt;
gt;
gt; --
gt;
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.5Thanks Joel
--
N/Aquot;Joelquot; wrote:

gt; Can anyone please help with this problem.
gt;
gt; I have a spreadsheet that notes everyones holidays, bank holidays and sick
gt; days. It looks like this in colum:
gt;
gt; (A2:B2) it has the name of the employee colum
gt; (D2) Total no of holidays
gt; (E2) Holidays Taken
gt; (F2) Holidays Left
gt; (G2) Total no of bank holidays
gt; (H2) Bank Holidays Taken
gt; (I2) Bank Holidays Left
gt; (M4) Date (Sun 02/04/06)
gt; (N4) Date (Mon 03/04/06) the next cell as ther following date and so on
gt;
gt; The year is split in to two lots of 6 months because the spreadsheet doesn't
gt; have enough colums
gt;
gt; (A19:B19) it has the name of the employee colum
gt; (D19) Total no of holidays
gt; (E19) Holidays Taken
gt; (F19) Holidays Left
gt; (G19) Total no of bank holidays
gt; (H19) Bank Holidays Taken
gt; (I19) Bank Holidays Left
gt; (M21) Date (Sun 01/10/06)
gt; (N21) Date (Mon 01/10/06) the next cell as ther following date and so on
gt;
gt; It works works like You have the name quot;Fred Bloggsquot; who as for example 20
gt; days holiday, no days taken and 20 left
gt;
gt; If a day has a 'H' int it it counts it as one holiday the same witth the
gt; days if the have a 'BH' in or a 'S' in, it then keeps a running tottal in a
gt; colum for me.
gt;
gt; The problem I have is I now wish it to cou'H/2 vas have a day and not a
gt; whole day same for 'S' and 'BH'. I amm just very unsure on how to enter it in
gt; the formula window.
gt;
gt; The code I have at the moment is as follows for holiday for fred bloggs is
gt; as follows
gt;
gt; =COUNTIF(M6:GL6,quot;hquot;) (COUNTIF(M23:GL23,quot;hquot;))
gt;
gt;
gt; --
gt;

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)

quot;Joelquot; gt; wrote in message
...
gt; Than you bob for that what I am after is when it is H/2 or half I need it
to
gt; count it as 0.5
gt;
gt;
gt; Thanks Joel
gt; --
gt; N/A
gt;
gt;
gt; quot;Joelquot; wrote:
gt;
gt; gt; Can anyone please help with this problem.
gt; gt;
gt; gt; I have a spreadsheet that notes everyones holidays, bank holidays and
sick
gt; gt; days. It looks like this in colum:
gt; gt;
gt; gt; (A2:B2) it has the name of the employee colum
gt; gt; (D2) Total no of holidays
gt; gt; (E2) Holidays Taken
gt; gt; (F2) Holidays Left
gt; gt; (G2) Total no of bank holidays
gt; gt; (H2) Bank Holidays Taken
gt; gt; (I2) Bank Holidays Left
gt; gt; (M4) Date (Sun 02/04/06)
gt; gt; (N4) Date (Mon 03/04/06) the next cell as ther following date and so on
gt; gt;
gt; gt; The year is split in to two lots of 6 months because the spreadsheet
doesn't
gt; gt; have enough colums
gt; gt;
gt; gt; (A19:B19) it has the name of the employee colum
gt; gt; (D19) Total no of holidays
gt; gt; (E19) Holidays Taken
gt; gt; (F19) Holidays Left
gt; gt; (G19) Total no of bank holidays
gt; gt; (H19) Bank Holidays Taken
gt; gt; (I19) Bank Holidays Left
gt; gt; (M21) Date (Sun 01/10/06)
gt; gt; (N21) Date (Mon 01/10/06) the next cell as ther following date and so on
gt; gt;
gt; gt; It works works like You have the name quot;Fred Bloggsquot; who as for example
20
gt; gt; days holiday, no days taken and 20 left
gt; gt;
gt; gt; If a day has a 'H' int it it counts it as one holiday the same witth
the
gt; gt; days if the have a 'BH' in or a 'S' in, it then keeps a running tottal
in a
gt; gt; colum for me.
gt; gt;
gt; gt; The problem I have is I now wish it to cou'H/2 vas have a day and not a
gt; gt; whole day same for 'S' and 'BH'. I amm just very unsure on how to enter
it in
gt; gt; the formula window.
gt; gt;
gt; gt; The code I have at the moment is as follows for holiday for fred bloggs
is
gt; gt; as follows
gt; gt;
gt; gt; =COUNTIF(M6:GL6,quot;hquot;) (COUNTIF(M23:GL23,quot;hquot;))
gt; gt;
gt; gt;
gt; gt; --
gt; gt;
If you don't like my suggestion,. I suppose that you could use

=SUMPRODUCT(-(ISNUMBER(FIND(quot;H/2quot;,M6:GL6)))/2
ISNUMBER(FIND(quot;Hquot;,M6:GL6)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Joelquot; gt; wrote in message
...
gt; Than you bob for that what I am after is when it is H/2 or half I need it
to
gt; count it as 0.5
gt;
gt;
gt; Thanks Joel
gt; --
gt; N/A
gt;
gt;
gt; quot;Joelquot; wrote:
gt;
gt; gt; Can anyone please help with this problem.
gt; gt;
gt; gt; I have a spreadsheet that notes everyones holidays, bank holidays and
sick
gt; gt; days. It looks like this in colum:
gt; gt;
gt; gt; (A2:B2) it has the name of the employee colum
gt; gt; (D2) Total no of holidays
gt; gt; (E2) Holidays Taken
gt; gt; (F2) Holidays Left
gt; gt; (G2) Total no of bank holidays
gt; gt; (H2) Bank Holidays Taken
gt; gt; (I2) Bank Holidays Left
gt; gt; (M4) Date (Sun 02/04/06)
gt; gt; (N4) Date (Mon 03/04/06) the next cell as ther following date and so on
gt; gt;
gt; gt; The year is split in to two lots of 6 months because the spreadsheet
doesn't
gt; gt; have enough colums
gt; gt;
gt; gt; (A19:B19) it has the name of the employee colum
gt; gt; (D19) Total no of holidays
gt; gt; (E19) Holidays Taken
gt; gt; (F19) Holidays Left
gt; gt; (G19) Total no of bank holidays
gt; gt; (H19) Bank Holidays Taken
gt; gt; (I19) Bank Holidays Left
gt; gt; (M21) Date (Sun 01/10/06)
gt; gt; (N21) Date (Mon 01/10/06) the next cell as ther following date and so on
gt; gt;
gt; gt; It works works like You have the name quot;Fred Bloggsquot; who as for example
20
gt; gt; days holiday, no days taken and 20 left
gt; gt;
gt; gt; If a day has a 'H' int it it counts it as one holiday the same witth
the
gt; gt; days if the have a 'BH' in or a 'S' in, it then keeps a running tottal
in a
gt; gt; colum for me.
gt; gt;
gt; gt; The problem I have is I now wish it to cou'H/2 vas have a day and not a
gt; gt; whole day same for 'S' and 'BH'. I amm just very unsure on how to enter
it in
gt; gt; the formula window.
gt; gt;
gt; gt; The code I have at the moment is as follows for holiday for fred bloggs
is
gt; gt; as follows
gt; gt;
gt; gt; =COUNTIF(M6:GL6,quot;hquot;) (COUNTIF(M23:GL23,quot;hquot;))
gt; gt;
gt; gt;
gt; gt; --
gt; gt;
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/Aquot;Joelquot; wrote:

gt; Than you bob for that what I am after is when it is H/2 or half I need it to
gt; count it as 0.5
gt;
gt;
gt; Thanks Joel
gt; --
gt; N/A
gt;
gt;
gt; quot;Joelquot; wrote:
gt;
gt; gt; Can anyone please help with this problem.
gt; gt;
gt; gt; I have a spreadsheet that notes everyones holidays, bank holidays and sick
gt; gt; days. It looks like this in colum:
gt; gt;
gt; gt; (A2:B2) it has the name of the employee colum
gt; gt; (D2) Total no of holidays
gt; gt; (E2) Holidays Taken
gt; gt; (F2) Holidays Left
gt; gt; (G2) Total no of bank holidays
gt; gt; (H2) Bank Holidays Taken
gt; gt; (I2) Bank Holidays Left
gt; gt; (M4) Date (Sun 02/04/06)
gt; gt; (N4) Date (Mon 03/04/06) the next cell as ther following date and so on
gt; gt;
gt; gt; The year is split in to two lots of 6 months because the spreadsheet doesn't
gt; gt; have enough colums
gt; gt;
gt; gt; (A19:B19) it has the name of the employee colum
gt; gt; (D19) Total no of holidays
gt; gt; (E19) Holidays Taken
gt; gt; (F19) Holidays Left
gt; gt; (G19) Total no of bank holidays
gt; gt; (H19) Bank Holidays Taken
gt; gt; (I19) Bank Holidays Left
gt; gt; (M21) Date (Sun 01/10/06)
gt; gt; (N21) Date (Mon 01/10/06) the next cell as ther following date and so on
gt; gt;
gt; gt; It works works like You have the name quot;Fred Bloggsquot; who as for example 20
gt; gt; days holiday, no days taken and 20 left
gt; gt;
gt; gt; If a day has a 'H' int it it counts it as one holiday the same witth the
gt; gt; days if the have a 'BH' in or a 'S' in, it then keeps a running tottal in a
gt; gt; colum for me.
gt; gt;
gt; gt; The problem I have is I now wish it to cou'H/2 vas have a day and not a
gt; gt; whole day same for 'S' and 'BH'. I amm just very unsure on how to enter it in
gt; gt; the formula window.
gt; gt;
gt; gt; The code I have at the moment is as follows for holiday for fred bloggs is
gt; gt; as follows
gt; gt;
gt; gt; =COUNTIF(M6:GL6,quot;hquot;) (COUNTIF(M23:GL23,quot;hquot;))
gt; gt;
gt; gt;
gt; gt; --
gt; gt;