Forum Discussion

Deleted's avatar
Deleted
Nov 07, 2017

Employee Attendance Tracker Question

Hi, all

I'm trying to understand how the template below works to make a new one that fits my need.

https://templates.office.com/en-us/Employee-attendance-tracker-TM02780235

In the conditional formatting, how does the formula work so that the days off are highlighted? Can someone explain please? 

excel
Formulas and Functions

6 Replies

    • 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
        Diamond Contributor
        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.

         

         

         

         

Resources