Oct 10 2020 08:37 AM
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.
Oct 10 2020 08:56 AM
That could be
=IF($D2<=E$1,"yes","no")
and drag it to other cells. If I understood your logic correctly, please check attached file.
Oct 11 2020 04:18 AM
@Sergei Baklan 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.
Oct 11 2020 06:51 AM
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
Oct 29 2020 05:05 AM
Oct 29 2020 01:27 PM
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.
Feb 07 2022 12:42 AM
@Sergei Baklan Hi i have a smiliar question trying to get monthly headcount for a particular department i have start dates and leave dates
Similar data to the person below but also leave dates.
I dont know what countifs function to use to get the monthly data for example all employees at X department up until 31st January 2022.
Feb 07 2022 04:58 AM
You need to define more carefully what the "monthly headcount" is.
For example, in department you had person who quit on 18 January 2022 and another person joined the company and this department on 14 January 2022.
Thus you have 1 person till 14th, 2 persons from 14th to 18th and 1 person from 18t till end of January. You may calculate number of persons on first of each month, or average, or on the end of each month, or use other combinations. Each has pros and cons. Perhaps on the end of the month is most common, such situation when you have 1 person during entire month, one more joined on 31st and we have headcount in this month = 2 are considered as acceptable.
Logic could be like
=SUMPRODUCT(
(Staff[Start Date] <= [@Month] ) *
( ( Staff[Quite Date] >= [@Month] ) +
( Staff[Quite Date] = "" )
) )