+ Reply to Thread
Results 1 to 6 of 6

Help in calculating retrospective headcount

  1. #1
    Registered User
    Join Date
    09-27-2012
    Location
    Mumbai, IN
    MS-Off Ver
    Office 365
    Posts
    97

    Help in calculating retrospective headcount

    Hi all,

    I have a users file which has their date of joining (all users) and date of exit (only for users which have left). This data is maintained since Jan 2016.

    What I want help with is- I have all months since Jan 2016 till date listed in column A of a sheet. I want to formulate a countif (or any other function) which can tell me how many employees were available in that month.

    I hope I have been able to put this in an easy manner for you to understand, if not I can elucidate.


    Regards

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,407

    Re: Help in calculating retrospective headcount

    It would be much easier to help with a sample workbook. The yellow banner at the top tells you how to attach one.
    Ali


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

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    09-27-2012
    Location
    Mumbai, IN
    MS-Off Ver
    Office 365
    Posts
    97

    Re: Help in calculating retrospective headcount

    Hi

    I have attached the file, please review
    Attached Files Attached Files

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Help in calculating retrospective headcount

    A
    B
    C
    D
    E
    F
    1
    Name
    DOJ
    DOE
    Month
    Souls
    2
    Employee 1
    3-Nov-15
    Nov-15
    2
    3
    Employee 2
    16-Nov-15
    25-Jun-18
    Dec-15
    2
    4
    Employee 3
    11-Apr-16
    6-Jul-18
    Jan-16
    2
    5
    Employee 4
    4-Jul-16
    Feb-16
    2
    6
    Employee 5
    4-Jul-16
    Mar-16
    2
    7
    Employee 6
    4-Jul-16
    Apr-16
    3
    8
    Employee 7
    13-Oct-16
    May-16
    3
    9
    Employee 8
    14-Feb-17
    3-Apr-19
    Jun-16
    3
    10
    Employee 9
    1-Mar-17
    Jul-16
    6
    11
    Employee 10
    2-Mar-17
    Aug-16
    6
    12
    Employee 11
    17-Mar-17
    Sep-16
    6
    13
    Employee 12
    20-Mar-17
    6-Jul-18
    Oct-16
    7
    14
    Employee 13
    22-Mar-17
    Nov-16
    7
    15
    Employee 14
    23-Mar-17
    Dec-16
    7
    16
    Employee 15
    23-Mar-17
    2-May-19
    Jan-17
    7
    17
    Employee 16
    24-Mar-17
    6-May-19
    Feb-17
    8
    18
    Employee 17
    3-Apr-17
    29-Mar-19
    Mar-17
    16
    19
    Employee 18
    3-Apr-17
    15-Mar-19
    Apr-17
    20
    20
    Employee 19
    21-Apr-17
    29-Mar-19
    May-17
    22
    21
    Employee 20
    24-Apr-17
    Jun-17
    23
    22
    Employee 21
    17-May-17
    Jul-17
    24
    23
    Employee 22
    30-May-17
    Aug-17
    25


    I removed the hyphens from the DOE column. The formula in F2 and copied down is
    Please Login or Register  to view this content.
    The answer is a little fuzzy because it counts the people that were hired before the end of the month and left after the beginning (or haven't left), which may not represent the actual headcount at any point.
    Last edited by shg; 12-17-2019 at 02:58 PM.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    09-27-2012
    Location
    Mumbai, IN
    MS-Off Ver
    Office 365
    Posts
    97

    Re: Help in calculating retrospective headcount

    Yeah I sense that, any other ways?

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Help in calculating retrospective headcount

    To do what?

+ 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. Headcount per interval
    By sregnov in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-15-2019, 01:12 AM
  2. Calculating Employee Headcount by month.
    By jekeith in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-25-2018, 10:44 PM
  3. [SOLVED] Headcount Forecasting
    By Shaun_Chetty in forum Excel General
    Replies: 17
    Last Post: 05-27-2015, 02:45 PM
  4. Headcount Lookup
    By sasha38 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-08-2014, 12:21 PM
  5. Replies: 1
    Last Post: 07-17-2013, 11:34 AM
  6. Headcount Within Month
    By som3on3_10 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-25-2009, 03:37 AM
  7. [SOLVED] Retrospective worksheet change event?
    By Meltad in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-08-2006, 12:10 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