vacation formula and 1/2 days

[Wanna Learn]

Hello
I have an attendance tracking workbook. one of the formulas is
=COUNTIF($I6:$BU6, "V") V stands for vacation day. Sometimes an
employee takes ½ a day. E.g. If an employees has taken 5.5 vacation days
with the formula above the total count is 6, incorrect by ½ day. I want to
use “V1†to indicate ½ day taken, What is the correct formula so that I get
5.5 days. Thank in advance

 

[Mike H]

Maybe

=COUNTIF($I6:$BU6, "V")+(COUNTIF($I6:$BU6, "V1")/2)

Mike

 

[Mike H]

on second thoughts this may be neater

=SUMPRODUCT(($I6:$BU6="V")+($I6:$BU6="V1")/2)


ike

 

[Carim]

Hi,

I would agree with Mike, and recommend to use Upper and Lower cases :
If cell C5 shows the word Holiday( and the quaterly data in range
F6:BS6), you can start using H for full days, and h for half days :

=SUMPRODUCT(EXACT(UPPER(LEFT(C$5,1)),$F6:$BS6)*1+EXACT(LOWER(LEFT(C
$5,1)),$F6:$BS6)*0.5)

HTH

 

[Tyro]

"V" stands for vacation day. A whole day. You need to have some way to
indicate less or more than a whole day. Perhaps you could express the
vacation time in hours.

Tyro

 

[domtom]

Hi all,
the thing is how excel will update the half day that the employee was
present i.e Half day vacation or Sick +Half day present
Could any body suggest a formula to auto update the fields that is the half
day present and the hald day v or S

Employee
mon tue we thur fri sat sun No.P No.L No.SIckLeave
off
V/2 P P S/2 P OFF O FF 4 0.5 0.5
2

 

[Fred Smith]

You'll have no problem getting an answer as long as you ask a comprehensible
question. All you've done is prompted more questions. What does "V/2" mean?
What is "P"? What is "No.L"?

Your best bet is to give an example, and identify the outcome you are
looking for.

Regards,
Fred.

 

[Rick Rothstein \(MVP - VB\)]

Assuming the data shown is in Row 2 and that "mon" is in Column A, "tue" is
in Column B, etc. That means No.P is in Column H, No.L is in Column I,
No.SickLeave is in Column J and "off" is in Column K. Put the following
formulas in the indicated cells and then copy down...

H2: =COUNTIF(A2:G2,"P")+COUNTIF(A2:G2,"*/2")/2
I2: =COUNTIF(A2:G2,"V")+COUNTIF(A2:G2,"V/2")/2
J2: =COUNTIF(A2:G2,"S")+COUNTIF(A2:G2,"S/2")/2
K2: =COUNTIF(A2:G2,"OFF")+COUNTIF(A2:G2,"OFF/2")

I wasn't sure whether there was such a thing as OFF/2, so I put it in just
in case (if not, it won't hurt to leave it in anyway).

Rick

 

[domtom]

Hi Rick,
Many thanks for you help
Kind regards
Dominique