LinkBack URL
About LinkBacks
Hi!
I need a bit of help in figuring out what formula to use in order to calculate headcount/month/department, based on hire date & termination date.
Sample of my data:
excel.jpg
Any help is much appreciated!
Hi!
I need a bit of help in figuring out what formula to use in order to calculate headcount/month/department, based on hire date & termination date.
Sample of my data:
excel.jpg
Any help is much appreciated!
An attached workbook would be more help than a JPG, so I can see what format your dates are stored in...
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
here it is. Thanks!
sample headcount.xlsx
=SUMPRODUCT(IF($B$2:$B$10<H$1,1,0)*IF($C$2:$C$10>H$1,1,IF($C$2:$C$10="",1,0))*IF($D$2:$D$10=$G2,1,0))
In H2 as an array formula (confirm with ctrl+shift+enter)
copy down and across.
Please upload workbooks rather than images, means not having to type out all your data!
Last edited by ragulduy; 11-22-2013 at 11:39 AM.
sorry! I'll now for the future.
Question - my formula will evaluate based on the first of the month.
So in your example, when the employee starts on the 14th April, should they be included in the april figure?
similarly, if an employee terminates on the 14th April, should they be included in the april figure?
Note - you can do headcount for "at any point in month" by replacing all of the links to date cells with month(cell)
if an employee starts on 14 April - will be counted in April headcount and going forward
if the person leaven on 14 April - will be counted on all the months prior to leaving + April
ok so try:
=SUMPRODUCT(IF($B$2:$B$10<DATE(YEAR(H$1),MONTH(H$1)+1,DAY(H$1)),1,0)*IF($C$2:$C$10>DATE(YEAR(H$1),MONTH(H$1)-1,DAY(H$1)),1,IF($C$2:$C$10="",1,0))*IF($D$2:$D$10=$G2,1,0))
in H2 as an array formula (confirm with ctrl+shift+enter) and copy down and across
Hi there, Is anyone available to assist with this thread? I've tried the formula above and it is not working for me.
@abeltran618,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Ben Van Johnson
Hi I had a similar query and this formula worked perfectly - thank you so much
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks