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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
you could use a lookup table then use an IF statement to calculate teh 1s and 0s then have your total column reference this

Emp Name 1 2 3 4 5 6 7 8 TOTAL LEAVE
Name 1 P P L L H S P L 3
Name 2 P L L L H S L P 6
Name 3 P P P P H S P P 0




Hide this table
Emp Name 1 2 3 4 5 6 7 8 TOTAL LEAVE
Name 1 0 0 1 1 0 0 0 1 3
Name 2 0 1 1 1 1 1 1 0 6
Name 3 0 0 0 0 0 0 0 0 0
put this formula in "1" for Name 1 then assuming the first "P" in your table for "1" Name 1 is in Cell B4 then you can copythis formula to the other cells in the table
=IF(B4="S",IF(OR(A4="H",A4="L"),IF(OR(C4="H",C4="L"),1,0),0),IF(C4="S",IF(OR(D4="H",D4="L"),1,0),IF(B4="L",1,0)))

o
\__/\o
(Oo}
(=-) .===o- ~Z~A~P~
/'''''''\/U'
l l \_/
\\__)
E''''|
| ||
| || The
(__\\ Abductee
 
Upvote 0
Hi Abductee,

Your dormula only work for HS combination like LHSL whill be 4 but i have situation like LHL or LSL or LHHL in all these situations also H & S should be counted as leaves.

And one more things I had taken 1-8 days for sample but in actual it will be 1-30 or 1-31 or 1-28/29 colunms representing month days.

Regards!
 
Upvote 0
whats the difference between a H Holiday and L Leave?
is a Saturday always a Holiday?
what about bank holidays?
 
Upvote 0
I am writing my problem again.
I want to calculate leaves taken by any employee. So any "L" will be counted.
But if any employee had taken leave prior to sunday OR holiday and after sunday or holiday than those sunday and holidays should be counted as leave. here there is no specific list of holidays.

for eg: Leave - Holiday - Leave should give 3
Leave - Holiday - Sunday - Leave should give 4
Leave - Holiday - Present should give 1
Present - Holiday- Leave - Sunday- Leave should give 3

Just advise if you need more clarification on this.

Thanks & Regards!
SM
 
Upvote 0
so

Leave - Holiday - Sunday - Present would give 1 ?

I still dont understand the difference between a Leave and a Holiday

And another problem is when Sunday is the last day of the month but the person then takes of the Monday as Leave, how will you capture this is you only go up to 31?
 
Upvote 0
yes Leave - Holiday - Sunday - Present will give 1.

Actually it is like this if employee want to take leave just before holiday or sunday and take another leave after the holiday or sunday he would think he had taken only 2 leaves but it should be 3 he cannot merge holiday or sunday by leaves and increse its leave period.

Saturday is a working day.
Differnce between leave and holiday is holiday is a declayered off and leave is taken by an employee.

Right Now I am working on month on month basis and did not consider the rolling of month to next month.

Regards!
 
Upvote 0
Maybe this:

Layout

Emp Name12345678910111213141516171819202122232425262728293031TOTAL LEAVEHelpCol
Name 1PPLLHSPL S S S 3PPLLHSPLSSS
Name 2PLLLHSLP S S S 6PLLLHSLPSSS
Name 3PPPPHSPP S S S 0PPPPHSPPSSS
Name 4PPPPHSPP S S S 0PPPPHSPPSSS
Name 5LLPPHSHLPLPPSHPHPPLSHHHHHPSLPPH6LLPPHSHLPLPPSHPHPPLSHHHHHPSLPPH
Name 6HHHHLSPHHLLLSPLLPLHSPPPHLLSHLPH12HHHHLSPHHLLLSPLLPLHSPPPHLLSHLPH
Name 7LPLHLSLHHHHPSPPPLLPSLPLPPLSPHLH12LPLHLSLHHHHPSPPPLLPSLPLPPLSPHLH
Name 8LPLPHSLLHHLHSPHHHLPSLHPPLHSPHHP10LPLPHSLLHHLHSPHHHLPSLHPPLHSPHHP
Name 9HPLHLSHLPHHPSHHLLPPSLHLLLLSLPPH16HPLHLSHLPHHPSHHLLPPSLHLLLLSLPPH
Name 10PHLHPSPPPHHHSPLLLHHSLHHPHPSLPLL8PHLHPSPPPHHHSPLLLHHSLHHPHPSLPLL
Name 11LHHLLSLHPHPLSLLPHPLSLPLHHLSHPHL19LHHLLSLHPHPLSLLPHPLSLPLHHLSHPHL
Name 12LPLLPSLLHLHHSPPPHLHSPPPHHPSPHLP9LPLLPSLLHLHHSPPPHLHSPPPHHPSPHLP
Name 13PLHHPSPPHPLLSPPPPPLSPHPLHPSHLHL8PLHHPSPPHPLLSPPPPPLSPHPLHPSHLHL
Name 14PHPPPSPPPLHLSPPPLPPSPPLHLLSLPLL12PHPPPSPPPLHLSPPPLPPSPPLHLLSLPLL
Name 15PPPPPSHPPPPPSLPLHHHSLPPLPPSLPPL6PPPPPSHPPPPPSLPLHHHSLPPLPPSLPPL
Name 16LPLPPSHPPLPPSPLHHPHSHPPHHHSPHPP4LPLPPSHPPLPPSPLHHPHSHPPHHHSPHPP
Name 17PLPPPSLHHHPLSPLPPPPSLHLLPHSLPLH10PLPPPSLHHHPLSPLPPPPSLHLLPHSLPLH
Name 18HPHPPSLPLPLHSPHHHLPSPHHHLLSPPHL7HPHPPSLPLPLHSPHHHLPSPHHHLLSPPHL
Name 19PPLLPSPPPPLLSHHPLHHSLPHPLHSPPPP7PPLLPSPPPPLLSHHPLHHSLPHPLHSPPPP
Name 20PPPPPSLPHHPLSLHHPPPSHPHLPPSPLHL8PPPPPSLPHHPLSLHHPPPSHPHLPPSPLHL
*************************************************************************************************************************

<colgroup><col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;" span="9"> <col width="19" style="width: 14pt; mso-width-source: userset; mso-width-alt: 694;" span="22"> <col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;"> <col width="198" style="width: 149pt; mso-width-source: userset; mso-width-alt: 7241;"> <tbody>
</tbody>


Formulas

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

=B2&C2&D2&E2&F2&G2&H2&I2&J2&K2&L2&M2&N2&O2&P2&Q2&R2&S2&T2&U2&V2&W2&X2&Y2&Z2&AA2&AB2&AC2&AD2&AE2&AF2

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+
COUNTIF(B2:AF2,"L")

Markmzz
 
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
374
sheri54371
S
B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
188
bh24524
B
F
  • Question
Rounding values to 2 decimel while keeping total 100%
2 3 4
Replies
38
Views
319
awoohaw
awoohaw
C
  • Question
Finding weekends and holidays within a date range
Replies
1
Views
323
ColinPearsonEIT
C
A
  • Solved
COUNT TWO OR MORE TEXT FROM RANGE WITH A SINGLE FORMULA
Replies
5
Views
277
Fluff
Fluff

Forum statistics

Threads
1,215,036
Messages
6,122,796
Members
449,095
Latest member
m_smith_solihull

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