Count Function

[Steve]

I am using the count function for attendance tracking of Vacation, Personal
Time, & Sick Time. (Example: =COUNTIF($F6:$CQ6, "V")

Problem is that, now I need to be able to do half days. I have tried many
different formulas/ways to incorporate the half day scenario even without
using the count function with no success.

Do anybody have any ideas?

 

[Anne Troy]

I think you're telling us you can't figure out a code for half days? I
mean... if it's a half day vacation, why not HV? Then: =countif($F6:$CQ6,
"V")+(countif($F6:$CQ6, "HV")*.5)
?
************
Anne Troy
VBA Project Manager
www.OfficeArticles.com

 

[Nise]

need more info. how do you represent half day in your sheet.

 

[Steve]

That worked Thx so much you have no idea how long I have spent on this.

 

[Steve]

Solved the problem thx for your help.

 

[Bob Phillips]

Steve,

The way I do this is to use an upper-case letter for a full day, lower-case
for a half-day. SO, assuming my absence code is in A1 (upper or lower), I
use

=SUMPRODUCT(--(ISNUMBER(FIND(LOWER(A2),A1:M1)))/2+ISNUMBER(FIND(UPPER(A2),A1
:M1)))

to calculate the total.

--

HTH

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

 

[Anne Troy]

VERY cool.
************
Anne Troy
VBA Project Manager
www.OfficeArticles.com

 

[Steve]

Anne:

One more question for you. I also have a conditional format saying that if
formula is =G6="v" then the cell would turn color. How would I incorporate
that into the HV scenario?

 

[Anne Troy]

So you want it to turn to the same color if it's V or HV?
I think you just need to set a 2nd condition same as the first, but HV
instead of V.
Got me?
************
Anne Troy
VBA Project Manager
www.OfficeArticles.com

 

[Roger Govier]

Hi Steve

=OR(G6="v",G6="hv")

Regards

Roger Govier

 

[Anne Troy]

I knew it! (That's why my reply said "I think".) Thanks, Roger.
************
Anne Troy
VBA Project Manager
www.OfficeArticles.com

 

[Roger Govier]

Hi Anne
I hadn't seen your response when posting, but looking at it now, you did say
the same thing. I guess I just gave the formula rather than the reason.
You "thunk" correctly<vbg>.

Regards

Roger Govier

 

[bplumhoff]

Hi Steve,

We use a quite simple spreadsheet in 2 companies with ca. 25 employees
each:

http://www.sulprobil.com/html/vacation_plan.html

HTH,
Bernd

 

[Anne Troy]

Oh, no. I meant I knew there had to be a formula that would do it in one
format, rather than using two...
You supplied it.
************
Anne Troy
VBA Project Manager
www.OfficeArticles.com