Forum Discussion
Employee Attendance Tracker Question
Hi Cheng
Here is the discussion https://techcommunity.microsoft.com/t5/Excel/Employee-attendance-tracker-using-excel/m-p/124045/highlight/false#M2681 if help
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.
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.
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?