# Count of Employees Absent on a Particular Date

1. ## Count of Employees Absent on a Particular Date

Hi,

I am using the "Employee Attendance Tracker" template from Excel 2013 templates section (attached). The template is fine as is but I want it to do two more things:

1. I want the entire row of the current month to be highlighted automatically on the 1st Tab (Calendar View)

2. I need the template to count the total number of employees that are absent on any given date.
Edit: Just to be clear, I need the total number of absentee employees on a given date in that same year in a separate table somewhere below the Key Statistics Area on the Calendar View.

For example:

If 4 employees are absent on 28th July 2016, I want 4 to be displayed against 28th July in a separate table somewhere on the 1st Tab (Calendar View).

Is there any way to achieve this?

2. ## Re: Count of Employees Absent on a Particular Date

Heres the first part. use conditional formatting to achieve this, using this formula:
=TEXT(NOW(),"mmmm")=TEXT(C9,"mmmm")

3. ## Re: Count of Employees Absent on a Particular Date

Bad reply. My first will light up thae current month in every year. This will light up the current month in the current year ONLY.

=TEXT(NOW(),"mmmm yyyy")=TEXT(\$C9,"mmmm yyyy")

4. ## Re: Count of Employees Absent on a Particular Date

Thank you Glenn! That worked perfectly.

Cheers on the quick turnaround too!

5. ## Re: Count of Employees Absent on a Particular Date

For the number of employees absent today, try this somewhere on the calendar page (I've moved the title box up and added this formula to a merged cell below the title):

="Absent today "&TEXT(TODAY(),"dd mmmm yyyy")&": "&COUNTIFS(tblLeave[Start Date],"<="&TODAY(),tblLeave[End Date],">="&TODAY())

6. ## Re: Count of Employees Absent on a Particular Date

Hi Ali,

Thank you for your reply. This gives me the particular day's count. Is there any way to store all of these counts in a similar format as the Calendar view with months and dates? Because this count will vanish once the next day begins, right?

I mean, just like the huge table on "Calendar View" worksheet, can a similar one be created just for the count of leaves?

7. ## Re: Count of Employees Absent on a Particular Date

Sorry - you've lost me. This is what you asked for:

If 4 employees are absent on 28th July 2016, I want 4 to be displayed against 28th July in a separate table somewhere on the 1st Tab (Calendar View).
And now you say:

This gives me the particular day's count.
Yes, it does - why are you surprised when that is what you asked for? Then you asked:

I mean, just like the huge table on "Calendar View" worksheet, can a similar one be created just for the count of leaves?
I have no clue what it is that you are asking for here - sorry. Can you provide some sample data?

8. ## Re: Count of Employees Absent on a Particular Date

I've attached the file.

Just scroll down on the "Calendar View" worksheet to see how I need the employee count to be populated. So basically, what your formula does is fine but I want it published for each day of the year in a separate table below.

Edit: I mistyped "leave" count in the 2nd table on the Calendar View. Please ignore that and treat it as employee count instead.

9. ## Re: Count of Employees Absent on a Particular Date

Sorry, I'm still not quite clear. All I can see at the bottom of the page is an incorrect version of the calendar with dates on it. How do you want it to appear? Give us an example of the sort of data you expect to see there, please.

10. ## Re: Count of Employees Absent on a Particular Date

I'm reattaching the file with what I need in the 2nd part of my question precisely - The total absentee count of a given day in that same year.

For example: 10 people are absent on 12th June 2016.

11. ## Re: Count of Employees Absent on a Particular Date

Try this in H41 copied across and down:

=COUNTIFS(tblLeave[Start Date],"<="&H9,tblLeave[End Date],">="&H9)

You could modify it this:

=if(COUNTIFS(tblLeave[Start Date],"<="&H9,tblLeave[End Date],">="&H9)=0,"",COUNTIFS(tblLeave[Start Date],"<="&H9,tblLeave[End Date],">="&H9))

12. ## Re: Count of Employees Absent on a Particular Date

Tried both but the counts seem a bit off.

3rd Jan, 31st Jan, 1st Feb and 2nd Feb are all leaves. Assuming that at least 1 employee was off on all 4 days, the corresponding counts in the table should be 1 instead of 0.

13. ## Re: Count of Employees Absent on a Particular Date

Sorry - I've reached the end of the line with this one. Hopefully someone else will see how it can be done.

14. ## Re: Count of Employees Absent on a Particular Date

Is this what you had in mind? There are 3 formulae in use, the coloured bars represent where they change. there is also one silly sick leave in the raw data (highlighted). it involved time travel!!

15. ## Re: Count of Employees Absent on a Particular Date

Yessir! Thank you so much!

This is exactly what I wanted.

16. ## Re: Count of Employees Absent on a Particular Date

You're welcome!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1