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

[Guest]

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

 

[Bob Phillips]

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",D120))))+=SUMPRODUCT(--(ISNUMBER(FIND("l"
,D120))))/2

--

HTH

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

 

[Bob Phillips]

sorry superfluous =

=SUMPRODUCT(--(ISNUMBER(FIND("L",D120))))+SUMPRODUCT(--(ISNUMBER(FIND("l"
,D120))))/2


--

HTH

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

 

[Jason Morin]

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

=SUMPRODUCT(ISNUMBER(FIND({"l","L"},D120))*{0.5,1})

HTH
Jason
Atlanta, GA

 

[Bob Phillips]

Smart-ass

I think I might just use that!

Regards

Bob

 

[Jason Morin]

-----Original Message-----
Smart-ass

I think I might just use that!

Regards

Bob





.