How do you calc half day annual leave on a xls spread sheet using.
 

I am trying to create a spreadsheet showing annual leave in a workbook. I
have used countif to calc number of days. but how do i show half days using
formulas.

many thanks

Richard.Gayle

Richard,

I use a lower case l for half-day, upper-case L for a full-day and then
count them with

=SUMPRODUCT(--(ISNUMBER(FIND("L",D1:D20))))+=SUMPRODUCT(--(ISNUMBER(FIND("l"
,D1:D20))))/2

--

HTH

RP
(remove nothere from the email address if mailing direct)


"RGayle_Imperial" wrote in
message ...
I am trying to create a spreadsheet showing annual leave in a workbook. I
have used countif to calc number of days. but how do i show half days
using
formulas.

many thanks

Richard.Gayle

sorry superfluous =

=SUMPRODUCT(--(ISNUMBER(FIND("L",D1:D20))))+SUMPRODUCT(--(ISNUMBER(FIND("l"
,D1:D20))))/2


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
Richard,

I use a lower case l for half-day, upper-case L for a full-day and then
count them with


=SUMPRODUCT(--(ISNUMBER(FIND("L",D1:D20))))+=SUMPRODUCT(--(ISNUMBER(FIND("l"
,D1:D20))))/2

--

HTH

RP
(remove nothere from the email address if mailing direct)


"RGayle_Imperial" wrote in
message ...
I am trying to create a spreadsheet showing annual leave in a workbook.
I
have used countif to calc number of days. but how do i show half days
using
formulas.

many thanks

Richard.Gayle

Same concept as what Bob's given you, just shorter:

=SUMPRODUCT(ISNUMBER(FIND({"l","L"},D1:D20))*{0.5, 1})

HTH
Jason
Atlanta, GA

-----Original Message-----
I am trying to create a spreadsheet showing annual leave
in a workbook. I
have used countif to calc number of days. but how do i
show half days using
formulas.

many thanks

Richard.Gayle
.

Smart-ass :-)

I think I might just use that!

Regards

Bob


"Jason Morin" wrote in message
...
Same concept as what Bob's given you, just shorter:

=SUMPRODUCT(ISNUMBER(FIND({"l","L"},D1:D20))*{0.5, 1})

HTH
Jason
Atlanta, GA

-----Original Message-----
I am trying to create a spreadsheet showing annual leave
in a workbook. I
have used countif to calc number of days. but how do i
show half days using
formulas.

many thanks

Richard.Gayle
.

<LOL

-----Original Message-----
Smart-ass :-)

I think I might just use that!

Regards

Bob


"Jason Morin" wrote
in message
...
Same concept as what Bob's given you, just shorter:

=SUMPRODUCT(ISNUMBER(FIND({"l","L"},D1:D20))*{0.5, 1})

HTH
Jason
Atlanta, GA

-----Original Message-----
I am trying to create a spreadsheet showing annual
leave
in a workbook. I
have used countif to calc number of days. but how do
i
show half days using
formulas.

many thanks

Richard.Gayle
.



.