Employee Attendance Tracker Question

[Deleted]

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? 

[#2]

Hi Cheng

Here is the discussion https://techcommunity.microsoft.com/t5/Excel/Employee-attendance-tracker-using-excel/m-p/124045/high... if help

[#3]

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. 

[#4]

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.

[#5]

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?

[#6]

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.

[#7]

Is there a formula I can use on this spreadsheet to put a leave remaining specific to the person selected on the calendar view tab?