Employee Attendance Tracker Question

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.
- Deleted
  Nov 10, 2017
  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
    Diamond Contributor
    Nov 10, 2017
    Hi Cheng,
    
    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 table
    Apply the second condition, how many records for Employee 1 have Start Date before Jan 03, 2017
    =COUNTIFS(lstEmpNames,valSelEmployee,lstSdates,"<="&E6)
    5 such records
    On the top one more condition, select within above the end date not later than Jan 03, 2017
    =COUNTIFS(lstEmpNames,valSelEmployee,lstSdates,"<="&E6,lstEDates,">="&E6)
    Only one such record.
    And finally is above one record of Seak Leave type
    =COUNTIFS(lstEmpNames,valSelEmployee,lstSdates,"<="&E6,lstEDates,">="&E6,lstHTypes,'Leave Types'!$B$4)
    Yes, it is.
    Finally COUNTIFS() returns what one record meets all above conditions together, which is >0, thus we highlight the cell.

Forum Discussion

Resources