Prabhu_Churi
New Member
- Joined
- Oct 2, 2022
- Messages
- 2
- Office Version
- 2016
- Platform
- Windows
I have enclosed template for Utilization. What if the employee works in holidays or in weekends? how we can get the data in summary sheets.
Worked days will increase Can i get the separate column like as total comp offs(worked in Holidays and weekends) for each employees
Formula As Below-
IFERROR(@IF(C$7="","NA",IF(C$7<@INDEX(T_EMP[START DATE],ROW($B8)-ROW($B$7)),"NE",IF(AND(@INDEX(T_EMP[TERMINATION DATE],ROW($B8)-ROW($B$7))>0,C$7>@INDEX(T_EMP[TERMINATION DATE],ROW($B8)-ROW($B$7))),"NE",IF(NOT(ISERROR(MATCH(C$7,L_HOLS,0))),"H",IF(@INDEX(L_WKNDVAL,WEEKDAY(C$7,1))=1,"WKND",INDEX(T_LEAVE[LEAVE TYPE],SUMPRODUCT(--(T_LEAVE[EMPLOYEE NAME]=$B8),--(T_LEAVE[START DATE]<=C$7),--(T_LEAVE[END DATE]>=C$7),ROW(T_LEAVE[LEAVE TYPE]))-@ROW(T_LEAVE[#Headers]))))))),"")
Worked days will increase Can i get the separate column like as total comp offs(worked in Holidays and weekends) for each employees
Formula As Below-
IFERROR(@IF(C$7="","NA",IF(C$7<@INDEX(T_EMP[START DATE],ROW($B8)-ROW($B$7)),"NE",IF(AND(@INDEX(T_EMP[TERMINATION DATE],ROW($B8)-ROW($B$7))>0,C$7>@INDEX(T_EMP[TERMINATION DATE],ROW($B8)-ROW($B$7))),"NE",IF(NOT(ISERROR(MATCH(C$7,L_HOLS,0))),"H",IF(@INDEX(L_WKNDVAL,WEEKDAY(C$7,1))=1,"WKND",INDEX(T_LEAVE[LEAVE TYPE],SUMPRODUCT(--(T_LEAVE[EMPLOYEE NAME]=$B8),--(T_LEAVE[START DATE]<=C$7),--(T_LEAVE[END DATE]>=C$7),ROW(T_LEAVE[LEAVE TYPE]))-@ROW(T_LEAVE[#Headers]))))))),"")
