Attendance Tracker Modification

Hi All

I am using a preloaded excel template for tracking employee attendance. I'm trying to modify it by adding additional code such as
P - Present
A - Absent
L - Late

What I want is that it will only count the following status tag as leave:
Sick Leave S
Vacation V
Maternity Leave M
Paternity Leave F
Comp OFF C
Half Day Leave 1 H1

And it will count separately the Present, Absent and Late separately in a monthly basis only (see picture below)
Excel Demo.png

Can you attached your file, please? It has not been filled with any 'sensitive' data.

Hello and welcome to the forum.

Personally I think this approach breaches my cardinal rule of not mixing up the two elements of data capture and final reporting. The two require quite different treatments. It's a very nicely presented calendar, and for some simple applications this approach it will no doubt work well.

For any serious analysis you should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So before you get too far with this I'd create a single sheet database that contains the following columns. I'd also consider treating a Presence as the default position so that you don't need to record anything.

Date
Employee *
Status Code *

The * values could be Validation drop down cells from which you could pick values. The way I normally arrange these things is to have a single data entry row above the database in which the new values are entered, then a button which runs a macro that adds the new record to the database. Or if you don't want to automate this aspect just add new records manually and directly in the table as they occur.

Once this is in place then a Pivot Table will allow you to report anything you want, and if you want a more prettified report you'll be able to us straightforward functions like =COUNTIFS()