Forum Discussion

    • Deleted's avatar
      Deleted
      Nov 07, 2017

      Hi, thanks for replying.

      I've read the thread.

      But it didn't mention how the formula 

      COUNTIFS(lstEmpNames,valSelEmployee,lstSdates,"<="&C6,lstEDates,">="&C6,lstHTypes,'Leave Types'!$B$4)>0

      works. 

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Nov 08, 2017

        Hi Cheng,

         

        All that names within the formula are references on tables columns on next sheets - Employees names, etc plus on some cells (e.g. employee name on first sheet). You may check what is what in name manager

        Concrete for this formula you count if

         

        COUNTIFS(lstEmpNames,valSelEmployee, -- employee name is in leave tracker AND
lstSdates,"<="&C6,  -- leave starts before the date in calendar cell AND
lstEDates,">="&C6,  -- leave ends after the date in calendar cell AND
lstHTypes,'Leave Types'!$B$4) -- leave type in the tracker is leave as in leave types table
>0

        if above >0, other words =TRUE, that means what the person was at leave at this date and the cell is highlighted.