Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 . |
#5
|
|||
|
|||
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 . |
#6
|
|||
|
|||
<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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using countif function to add only a half of a number | Excel Discussion (Misc queries) | |||
Calculate time difference to the half hour | Excel Worksheet Functions |