• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Looking for a Formula.

Hi All,

I am looking for a formula which counts the Employee leave throughout the complete years dates as available in attached sheet

I have created a formula but do not know how to add the Joining date available in cell B3 and Completion date available in cell C3 in the formula to count the employee leaves between the joining and completion dates. along with the condition if D2 = Probation then it should show the probation leaves if = Complete then it should show the leaves after completion date.

Any help would be highly appreciated.
 

Attachments

  • Book1.xlsx
    26.5 KB · Views: 13
LearnExcel&VBA
Please reread Forum Rules
From How to get the Best Results at Chandoo.org
You could find Use Relevant words in the Title and in the tag Box, This will aid future searches.
Your original Title would be something ... other.
 
Hi, Sure i will go through let me know you further that i have created this one please look into it.

=IF(D3="Complete","",COUNTIFS(Jan!B2:AF2,"L",Jan!B1:AF1,">"&B3,Jan!B1:AF1,"<"&C3)+COUNTIFS(Jan!B2:AF2,"H",Jan!B1:AF1,">"&B3,Jan!B1:AF1,"<"&C3)/2)+COUNTIFS(Jan!B2:AF2,"S",Jan!B1:AF1,">"&B3,Jan!B1:AF1,"<"&C3)/4

It is just for the month of Jan But i do not know how to add the Employee reference
 
Hi, Sure i will go through let me know you further that i have created this one please look into it.

=IF(D3="Complete","",COUNTIFS(Jan!B2:AF2,"L",Jan!B1:AF1,">"&B3,Jan!B1:AF1,"<"&C3)+COUNTIFS(Jan!B2:AF2,"H",Jan!B1:AF1,">"&B3,Jan!B1:AF1,"<"&C3)/2)+COUNTIFS(Jan!B2:AF2,"S",Jan!B1:AF1,">"&B3,Jan!B1:AF1,"<"&C3)/4

It is just for the month of Jan But i do not know how to add the Employee reference
Maybe,

Based on your formula, try this modified formula in E3 and copied down :

=IF(D3="Complete",0,COUNTIFS(INDIRECT(TEXT(B3,"mmm!")&ROW(A2)&":"&ROW(A2)),"L",INDIRECT(TEXT(B3,"mmm")&"!1:1"),">="&B3,INDIRECT(TEXT(B3,"mmm")&"!1:1"),"<="&C3)+COUNTIFS(INDIRECT(TEXT(B3,"mmm!")&ROW(A2)&":"&ROW(A2)),"H",INDIRECT(TEXT(B3,"mmm")&"!1:1"),">="&B3,INDIRECT(TEXT(B3,"mmm")&"!1:1"),"<="&C3)/2)+COUNTIFS(INDIRECT(TEXT(B3,"mmm!")&ROW(A2)&":"&ROW(A2)),"S",INDIRECT(TEXT(B3,"mmm")&"!1:1"),">="&B3,INDIRECT(TEXT(B3,"mmm")&"!1:1"),"<="&C3)/4

71219


Regards
 
bosco_yip

Hi, I hope you are doing well.

I've tried your formula it is working well but the formula is working for the month of "Jan" rather than all Jan-Dec.

If i use my formula that is short than yours for the month of Jan and add further Feb, Mar and so on. then it would become lengthy to adding other months in formula.

=IF(D3="Complete","",COUNTIFS(Jan!B2:AF2,"L",Jan!B1:AF1,">"&B3,Jan!B1:AF1,"<"&C3)+COUNTIFS(Jan!B2:AF2,"H",Jan!B1:AF1,">"&B3,Jan!B1:AF1,"<"&C3)/2)+COUNTIFS(Jan!B2:AF2,"S",Jan!B1:AF1,">"&B3,Jan!B1:AF1,"<"&C3)/4&""&

IF(D3="Complete","",COUNTIFS(Feb!B2:AF2,"L",Feb!B1:AF1,">"&B3,Feb!B1:AF1,"<"&C3)+COUNTIFS(Feb!B2:AF2,"H",Feb!B1:AF1,">"&B3,Feb!B1:AF1,"<"&C3)/2)+COUNTIFS(Feb!B2:AF2,"S",Feb!B1:AF1,">"&B3,Feb!B1:AF1,"<"&C3)/4&""&

IF(D3="Complete","",COUNTIFS(Mar!B2:AF2,"L",Mar!B1:AF1,">"&B3,Mar!B1:AF1,"<"&C3)+COUNTIFS(Mar!B2:AF2,"H",Mar!B1:AF1,">"&B3,Mar!B1:AF1,"<"&C3)/2)+COUNTIFS(Mar!B2:AF2,"S",Mar!B1:AF1,">"&B3,Mar!B1:AF1,"<"&C3)/4

and further....

Looking for a short formula solution your formula is perfect if it covers the all month.

If you can create further short formula i would really appreciate the help.
 
Last edited:
Maybe try

=IF(D3="Complete","",SUMPRODUCT(COUNTIFS(INDIRECT(TEXT(EDATE(360,ROW($A$1:$A$12)),"[$-en]mmm")&"!A1:AF1"),">="&B3,INDIRECT(TEXT(EDATE(360,ROW($A$1:$A$12)),"[$-en]mmm")&"!A1:AF1"),"<="&C3,
INDIRECT(TEXT(EDATE(360,ROW($A$1:$A$12)),"[$-en]mmm")&"!A"&ROW(A2)&":AF"&ROW(A2)),{"L","H","S"})/{1,2,4}))
 
Hi,

Non-volatile alternative:

=IF(D3="Complete","",SUMPRODUCT((MMULT(N(SIGN(FILTERXML("<a><b>"&TEXTJOIN("0</b><b>",0,Jan:Dec!B$1:AF$1)&"0</b></a>","//b")/10-B3:C3)<>{-1,1}),{1;1})=2)*(MID(FILTERXML("<a><b>"&"ζ"&TEXTJOIN("ζ</b><b>ζ",0,Jan:Dec!B2:AF2)&"ζ</b></a>","//b"),2,1)={"L","H","S"})/{1,2,4}))

Regards
 
Back
Top