Employee Attendance Tracker Question

Question

Hi, allI'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-TM02780235In the conditional formatting, how does the formula work so that the days off are highlighted? Can someone explain please?&nbsp;

sergeibaklan · Answer

Hi Cheng&nbsp;Here is the discussion&nbsp;https://techcommunity.microsoft.com/t5/Excel/Employee-attendance-tracker-using-excel/m-p/124045/highlight/false#M2681 if help

deleted · Answer

Hi, thanks for replying.I've read the thread.But it didn't mention how the formula&nbsp;COUNTIFS(lstEmpNames,valSelEmployee,lstSdates,"&lt;="&amp;C6,lstEDates,"&gt;="&amp;C6,lstHTypes,'Leave Types'!$B$4)&gt;0works.&nbsp;

sergeibaklan · Answer

Hi Cheng,&nbsp;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 managerConcrete for this formula you count if&nbsp;COUNTIFS(lstEmpNames,valSelEmployee, -- employee name is in leave tracker AND
lstSdates,"&lt;="&amp;C6,  -- leave starts before the date in calendar cell AND
lstEDates,"&gt;="&amp;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
&gt;0if above &gt;0, other words =TRUE, that means what the person was at leave at this date and the cell is highlighted.&nbsp;&nbsp;&nbsp;&nbsp;

deleted · Answer

Thanks a lot!But the COUNTIFS formula in conditional formating doesn't seem to have anything to do with the data related to date in the calendar view sheet. So how does it select the cell to highlight? For example, if I select Employee 1, and 2017, the first range/criteria in COUNTIFS will always return 6, the 2nd pair returns 11, the 3rd returns 12, the 4th returns 6. How is the result 0 when they're put together? and How does that determine which dates to highlight?

sergeibaklan · Answer

Hi Cheng,&nbsp;Let take the cell E6 and apply formula to it=COUNTIFS(lstEmpNames,valSelEmployee)it returns we have 7 records for Employee 1 in Employee Leave Tracker tableApply the second condition, how many records for Employee 1 have Start Date before Jan 03, 2017=COUNTIFS(lstEmpNames,valSelEmployee,lstSdates,"&lt;="&amp;E6)5 such recordsOn the top one more condition, select within above the end date not later than Jan 03, 2017=COUNTIFS(lstEmpNames,valSelEmployee,lstSdates,"&lt;="&amp;E6,lstEDates,"&gt;="&amp;E6)Only one such record.And finally is&nbsp;above one record of&nbsp;Seak Leave type=COUNTIFS(lstEmpNames,valSelEmployee,lstSdates,"&lt;="&amp;E6,lstEDates,"&gt;="&amp;E6,lstHTypes,'Leave Types'!$B$4)Yes, it is.Finally COUNTIFS() returns what one record meets all above conditions together, which is &gt;0, thus we highlight the cell.&nbsp;&nbsp;

Forum Discussion

Employee Attendance Tracker Question

6 Replies

Resources