Forum Discussion

egspen2's avatar
egspen2
Copper Contributor
Oct 10, 2020

Monthly headcount based on hire date formula

I am looking to take an employee roster and, using a formula, put a "Yes" if the employee was active as of the respective month (based on comparing the hire date to the last day of the month in question) and "no" if they were hired after the month in question. Said differently, I am trying to create a monthly headcount based on hire date. 

Formulas and Functions

7 Replies

Sort By
    • Zeelynn's avatar
      Zeelynn
      Copper Contributor
      Oct 29, 2020
      How would you do a formula to grab the employees that were hired and rehired on or before a certain but excluded the ones that were terminated before that date?
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Oct 29, 2020

        Zeelynn 

        In general that's much more complex tax which is better to do with database or Excel tables whic imitates database. Tables could be

        - departments: ID and name

        -employees: ID, name, perhaps other static information like birthday

        - employees history: date, employee ID, department ID (in case of change), within it hiring and termination dates

        - calendar with each date for entire history

         

        Above is simplified, could be bit more complex

         

        After that Power Query itself or in combination with data model operations could generate desired reports.

         

        Less reliable way is with formulas, but least you need to have termination date in your records.

    • egspen2's avatar
      egspen2
      Copper Contributor
      Oct 11, 2020

      SergeiBaklan Thanks - this makes sense to me and is what I was trying to do. However, when I copy this same formula into my document, I get return different values (for example, I get a no when you get a yes). Did you have to re-format the date or anything with the source file? See attached for my sheet simply updated for the formula you suggested - not sure why we get different answers.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Oct 11, 2020

        egspen2 

        You keep information about the hire dates as texts, not as dates. At the same in columns headers somewhere are dates, somewhere are texts.

        I'd suggest to keep all dates as dates (which are actually numbers in Excel), not as texts. For that first convert Hire Date with Text to Columns into the dates

        After that be sure you have no texts in columns headers, change all texts on dates

        With that it shall work

         

Resources