+ Reply to Thread
Results 1 to 16 of 16

Count of Employees Absent on a Particular Date

  1. #1
    Registered User
    Join Date
    07-28-2016
    Location
    New Delhi, India
    MS-Off Ver
    2013
    Posts
    7

    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?
    Attached Files Attached Files
    Last edited by Cyn1c; 07-28-2016 at 06:19 AM. Reason: Solved!

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    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")
    Attached Files Attached Files
    Glenn



  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    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. #4
    Registered User
    Join Date
    07-28-2016
    Location
    New Delhi, India
    MS-Off Ver
    2013
    Posts
    7

    Re: Count of Employees Absent on a Particular Date

    Thank you Glenn! That worked perfectly.

    Cheers on the quick turnaround too!

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

    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())
    Last edited by AliGW; 07-28-2016 at 04:42 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  6. #6
    Registered User
    Join Date
    07-28-2016
    Location
    New Delhi, India
    MS-Off Ver
    2013
    Posts
    7

    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. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

    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. #8
    Registered User
    Join Date
    07-28-2016
    Location
    New Delhi, India
    MS-Off Ver
    2013
    Posts
    7

    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.
    Attached Files Attached Files
    Last edited by Cyn1c; 07-28-2016 at 05:21 AM.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

    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. #10
    Registered User
    Join Date
    07-28-2016
    Location
    New Delhi, India
    MS-Off Ver
    2013
    Posts
    7

    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.
    Attached Files Attached Files

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

    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. #12
    Registered User
    Join Date
    07-28-2016
    Location
    New Delhi, India
    MS-Off Ver
    2013
    Posts
    7

    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.
    Attached Files Attached Files

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

    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. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    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!!
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    07-28-2016
    Location
    New Delhi, India
    MS-Off Ver
    2013
    Posts
    7

    Re: Count of Employees Absent on a Particular Date

    Yessir! Thank you so much!

    This is exactly what I wanted.

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,810

    Re: Count of Employees Absent on a Particular Date

    You're welcome!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. how to get specific absent dates from employees attendance data
    By dineshjangir in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-13-2019, 02:50 PM
  2. absent employees. (Dates challenge:) )
    By QQQ_QQQ in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-07-2015, 08:44 AM
  3. [SOLVED] Count number days absent for different employees with multiple date ranges.
    By monkeypants in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-20-2015, 08:32 PM
  4. how to get specific absent dates from employees attendance data
    By dineshjangir in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-05-2014, 05:12 AM
  5. Count for present & absent days
    By Ajit Munj in forum Excel General
    Replies: 8
    Last Post: 10-08-2013, 01:13 AM
  6. [SOLVED] How to identify absent employees across 2 worksheets with results on a 3rd worksheet
    By jshimko in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-28-2013, 12:14 PM
  7. [SOLVED] Count of employees by date by region
    By tcowen in forum Excel General
    Replies: 3
    Last Post: 06-01-2012, 05:58 PM

Bookmarks

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