vacation formula and 1/2 days
 

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

vacation formula and 1/2 days
 

Maybe

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

Mike

"Wanna Learn" wrote:

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

vacation formula and 1/2 days
 

on second thoughts this may be neater

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


ike

"Wanna Learn" wrote:

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

vacation formula and 1/2 days
 

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+E XACT(LOWER(LEFT(C
$5,1)),$F6:$BS6)*0.5)

HTH

vacation formula and 1/2 days
 

"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

"Wanna Learn" wrote in message
...
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

vacation formula and 1/2 days
 

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

vacation formula and 1/2 days
 

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.

"domtom" wrote in message
...
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

vacation formula and 1/2 days
 

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" wrote in message
...
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

vacation formula and 1/2 days
 

Hi Rick,
Many thanks for you help
Kind regards
Dominique