Counting Holiday & Sunday as leaves

M

misrasomendra

Board Regular
Joined
Nov 17, 2012
Messages
74
Hi all excel experts,

Here I have a problem with counting number of leaves. Suppose below is my data
If you see I am counting total leaves for each employee. But here is one condition if between two leaves any sunday or any holiday will come that should also be counted as leaves.Like Name 2 there is one Holiday (H) and one Sunday(S) followed by and precedded by L. So they should also get counted as Leaves. There can be situations like PPLLLHHHLLLPP or PPPPLLSLPPH.
Emp Name12345678TOTAL LEAVE
Name 1PPLLHSPL3
Name 2PLLLHSLP6
Name 3PPPPHSPP0

<TBODY>
</TBODY>

Any help regarding what formula should deliver me the result. I think frequecy can do it but I am unable to figure out how?

Thanks in advance.

Regards!
SM.
 
Sort by date Sort by votes
What result would you expect with this data?

Emp Name12345678TOTAL LEAVE
Name 1HHLLPSLH?

<tbody>
</tbody>

If there are holidays at the start or end of the data, how do you know if they should be included (if you don't know whether the previous/next entry is "L" or "P" or whatever?) I assume the result will be 3 as you said you are not considering the rolling month
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sorry this also not giving correct result. See below

Em
Code: 
[TABLE="width: 1031"]
<TBODY>[TR]
[TD]Employee Name</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD="align: right"]3</SPAN>[/TD]
[TD="align: right"]4</SPAN>[/TD]
[TD="align: right"]5</SPAN>[/TD]
[TD="align: right"]6</SPAN>[/TD]
[TD="align: right"]7</SPAN>[/TD]
[TD="align: right"]8</SPAN>[/TD]
[TD="align: right"]9</SPAN>[/TD]
[TD="align: right"]10</SPAN>[/TD]
[TD="align: right"]11</SPAN>[/TD]
[TD="align: right"]12</SPAN>[/TD]
[TD="align: right"]13</SPAN>[/TD]
[TD="align: right"]14</SPAN>[/TD]
[TD="align: right"]15</SPAN>[/TD]
[TD="align: right"]16</SPAN>[/TD]
[TD="align: right"]17</SPAN>[/TD]
[TD="align: right"]18</SPAN>[/TD]
[TD="align: right"]19</SPAN>[/TD]
[TD="align: right"]20</SPAN>[/TD]
[TD="align: right"]21</SPAN>[/TD]
[TD="align: right"]22</SPAN>[/TD]
[TD="align: right"]23</SPAN>[/TD]
[TD="align: right"]24</SPAN>[/TD]
[TD="align: right"]25</SPAN>[/TD]
[TD="align: right"]26</SPAN>[/TD]
[TD="align: right"]27</SPAN>[/TD]
[TD="align: right"]28</SPAN>[/TD]
[TD="align: right"]29</SPAN>[/TD]
[TD="align: right"]30</SPAN>[/TD]
[TD="align: right"]31</SPAN>[/TD]
[TD]Total Leave</SPAN>[/TD]
[TD]Helper cell</SPAN>[/TD]
[/TR]
[TR]
[TD]Name 1</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]S</SPAN>[/TD]
[TD]L</SPAN>[/TD]
[TD]L</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]L</SPAN>[/TD]
[TD]H</SPAN>[/TD]
[TD]H</SPAN>[/TD]
[TD]S</SPAN>[/TD]
[TD]L</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]S</SPAN>[/TD]
[TD]L</SPAN>[/TD]
[TD]L</SPAN>[/TD]
[TD]L</SPAN>[/TD]
[TD]L</SPAN>[/TD]
[TD]L</SPAN>[/TD]
[TD]L</SPAN>[/TD]
[TD]S</SPAN>[/TD]
[TD]L</SPAN>[/TD]
[TD]H</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]12</SPAN>[/TD]
[TD]PPPPPPSLLPLHHSLPPPPPSLLLLLLSLHP</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL span=9><COL span=22><COL><COL></COLGROUP>[/TABLE]
1</SPAN>2</SPAN>3</SPAN>4</SPAN>5</SPAN>6</SPAN>7</SPAN>8</SPAN>9</SPAN>10</SPAN>11</SPAN>12</SPAN>13</SPAN>14</SPAN>15</SPAN>16</SPAN>17</SPAN>18</SPAN>19</SPAN>20</SPAN>21</SPAN>22</SPAN>23</SPAN>24</SPAN>25</SPAN>26</SPAN>27</SPAN>28</SPAN>29</SPAN>30</SPAN>31</SPAN>Total Leave</SPAN>Helper cell</SPAN>


It should give 15.

</SPAN>P</SPAN>P</SPAN>P</SPAN>P</SPAN>P</SPAN>S</SPAN>L</SPAN>L</SPAN>P</SPAN>L</SPAN>H</SPAN>H</SPAN>S</SPAN>L</SPAN>P</SPAN>P</SPAN>P</SPAN>P</SPAN>P</SPAN>S</SPAN>L</SPAN>L</SPAN>L</SPAN>L</SPAN>L</SPAN>L</SPAN>S</SPAN>L</SPAN>H</SPAN>P</SPAN>12</SPAN>PPPPPPSLLPLHHSLPPPPPSLLLLLLSLHP</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=9><COL span=22><COL><COL></COLGROUP>
 
Upvote 0
misrasomendra said:
Sorry this also not giving correct result.

It should give 15.
Click to expand...

Try this:

Code: 
In AG2 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=SUM(--(FREQUENCY(IFERROR(FIND({"LHL";"LSL"},AH26,COLUMN($B:$AF)-COLUMN($B:$B)+1),""),TRANSPOSE(ROW($1:$31)))>0))+
SUM(--(FREQUENCY(IFERROR(FIND({"LSHL";"LHSL";"LHHL"},AH26,COLUMN($B:$AF)-COLUMN($B:$B)+1),""),TRANSPOSE(ROW($1:$31)))>0))*2+
SUM(--(FREQUENCY(IFERROR(FIND({"LHHSL";"LHSHL";"LSHHL"},AH26,COLUMN($B:$AF)-COLUMN($B:$B)+1),""),TRANSPOSE(ROW($1:$31)))>0))*3+
COUNTIF(B26:AF26,"L")

PS: if you need more combination, the formula must to be modify.

Markmzz
 
Upvote 0
A small modification in my last formula:

Code: 
In AG2 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=SUM(--(FREQUENCY(IFERROR(FIND({"LHL";"LSL"},AH2,COLUMN($B:$AF)-COLUMN($B:$B)+1),""),TRANSPOSE(ROW($1:$31)))>0))+
SUM(--(FREQUENCY(IFERROR(FIND({"LSHL";"LHSL";"LHHL"},AH2,COLUMN($B:$AF)-COLUMN($B:$B)+1),""),TRANSPOSE(ROW($1:$31)))>0))*2+
SUM(--(FREQUENCY(IFERROR(FIND({"LHHSL";"LHSHL";"LSHHL";[COLOR="#FF0000"]"[B]LHHHL[/B]"[/COLOR]},AH2,COLUMN($B:$AF)-COLUMN($B:$B)+1),""),TRANSPOSE(ROW($1:$31)))>0))*3+
COUNTIF(B2:AF2,"L")

Markmzz
 
Upvote 0
Hi Markmzz

You gave a very nice solution.Thanks.. but can't there be any general solution wherein I don't have to pluck in the values manually as there can be many such possibilities.

Regards
 
Upvote 0
misrasomendra said:
Hi Markmzz

You gave a very nice solution.Thanks.. but can't there be any general solution wherein I don't have to pluck in the values manually as there can be many such possibilities.

Regards
Click to expand...

Hi Misrasomendra,

Here is my last formula with a small modification (a big formula):

Code: 
In AG2 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=SUM((FREQUENCY(IFERROR(FIND("L"&{"H";"S"}&"L",AH2,COLUMN($B:$AF)-COLUMN($B:$B)+1),""),TRANSPOSE(ROW($1:$31)))>0)+
(FREQUENCY(IFERROR(FIND("L"&{"SH";"HS";"HH"}&"L",AH2,COLUMN($B:$AF)-COLUMN($B:$B)+1),""),TRANSPOSE(ROW($1:$31)))>0)*2+
(FREQUENCY(IFERROR(FIND("L"&{"HHS";"HSH";"SHH";"HHH"}&"L",AH2,COLUMN($B:$AF)-COLUMN($B:$B)+1),""),TRANSPOSE(ROW($1:$31)))>0)*3+
(FREQUENCY(IFERROR(FIND("L"&{"HHHS";"HHSH";"HSHH";"SHHH";"HHHH"}&"L",AH2,COLUMN($B:$AF)-COLUMN($B:$B)+1),""),TRANSPOSE(ROW($1:$31)))>0)*4+
COUNTIF(B2:AF2,"L")


I'm sorry, but until now I don't have another way.

Maybe another user have another formula to solve your problem. Let's wait.

Markmzz
 
Last edited:
Upvote 0
Hi Misrasomendra,

If you don't have problem with VBA, then you can try this function (test with a copy of your workbook first):

Layout

Emp Name
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
TOTAL LEAVE
Name 1
P
P
L
L
H
S
P
L
S
S
S
3
Name 2
P
L
L
L
H
S
L
P
S
S
S
6
Name 3
P
P
P
P
H
S
P
P
S
S
S
0
Name 4
L
H
H
H
H
S
H
H
H
H
H
H
S
H
H
H
H
H
H
S
H
H
H
H
H
H
S
H
H
H
L
31
Name 5
L
L
H
H
H
S
L
P
H
P
P
H
S
P
H
P
H
H
P
S
L
L
L
L
H
L
S
P
P
P
L
14
Name 6
H
P
L
H
L
S
L
H
P
H
P
H
S
H
P
L
P
H
L
S
L
P
P
L
L
H
S
P
H
P
H
11
Name 7
P
L
H
P
H
S
L
L
H
P
H
H
S
H
L
P
L
P
P
S
H
P
H
H
H
P
S
P
H
P
P
5
Name 8
L
L
L
H
L
S
H
P
P
P
H
P
S
H
P
L
P
H
P
S
H
H
H
H
L
L
S
P
L
H
L
11
Name 9
L
H
L
L
H
S
H
L
H
L
H
L
S
P
L
H
H
P
P
S
L
H
H
P
H
P
S
L
P
L
H
16
Name 10
P
H
H
L
H
S
L
H
H
P
L
H
S
H
P
H
L
L
L
S
P
L
L
L
L
H
S
P
L
L
P
14
***********
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
************

<tbody>
</tbody>

Function

Code: 
Function mySum(myRng As Range) As Integer
    Dim myCell As Long, myMax As Long, myTot As Long, myTotRng As Long
    Dim myNxStr As String, myArrRng() As Variant
    myMax = 0
    myNxStr = ""
    myTotRng = myRng.Count
    myArrRng = myRng
    For myCell = 1 To myTotRng
        If myArrRng(1, myCell) = "" Then
            Exit For
        End If
        If myNxStr = "L" Then
            If myArrRng(1, myCell) = "H" Or myArrRng(1, myCell) = "S" Then
                myTot = myTot + 1
            End If
            If myArrRng(1, myCell - 1) <> "L" And myArrRng(1, myCell) = "L" Then
                myMax = myMax + myTot
                myTot = 0
            End If
        Else
            myTot = 0
        End If
        If myArrRng(1, myCell) = "L" Then
            myNxStr = "L"
            myMax = myMax + 1
        ElseIf myArrRng(1, myCell) = "P" Then
           myNxStr = ""
        End If
    Next myCell
    mySum = myMax
End Function

Formula

Code: 
In AG2 - use only Enter to enter the formula

=mySum(B2:AF2)

Markmzz
 
Upvote 0
Hi Markmzz,

Thanks again. Well VBA is another option I can use but I want a formula solution for this one. Lets wait for other experts to come up with their solution.

Regards.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Adding a key (So if someone clicks "P" in the excel, it will automatically turn that cell Purple with the letter P in it)
Replies
6
Views
487
sheri54371
S
B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
252
bh24524
B
F
  • Question
Rounding values to 2 decimel while keeping total 100%
2 3 4
Replies
38
Views
708
awoohaw
awoohaw
zookeepertx
  • Question
Having trouble "deciphering" some code so I can see how it works
Replies
7
Views
135
zookeepertx
zookeepertx
C
  • Question
Finding weekends and holidays within a date range
Replies
1
Views
385
ColinPearsonEIT
C

Forum statistics

Threads
1,216,106
Messages
6,128,863
Members
449,473
Latest member
soumyahalder4

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top