Counting dates in a RANGE (yargh!) :)
......A...............................I........... ..............J
1.......STATUS..........HIRE DATE.........TERM DATE 2.......Active..............1/24/1984...........12/12/2003 ~~~... .............. ........... . ...... ............. 5403..Terminated.....3/14/2005............5/24/2006 So I have this large list of employee data. I want to find out the base number of employees who were here in 2004. What I want to do is COUNT all "Active" employees in the list, then I want to COUNT all those who were hired AFTER (2004,12,31), and finally COUNT all those who left [or. "term dated"] DURING 2004. Once I have those three numbers, I simply take all Active employees, subtract those hired after 2004 and then add back those who quit during 2004 to give me the base number on January 1st, 2004. Formulas I've used are as follows (along with their results) --- **Counting all Actives =COUNTIF(A1:A5403,"Active") Nice and easy, and equals 747. **Counting all those hired after 2004 =COUNTIF(I1:I5403,""&DATE(2004,12,31)) A bit more complicated, but works quite nicely. I originally had HUGE problems due to a sorted list and the dates being input as '12/28/2000 etc... I think the ' was buggering everything. Data -- Text to Columns fixed it. **Counting all those who quit DURING 2004 [ie. Jan1st'04<= and <Dec31st'04 .... this is where I'm stumped. I know that COUNTIF does not accept multiple arguments but dont know what to use in place of it. If anyone has some advice, please lend it! PS - this NG is great, never seen such helpful knowledgeable people |
Counting dates in a RANGE (yargh!) :)
Step three (counting dates between ranges) works by using:
=SUMPRODUCT(--(FM!J$3:J$5403$E$1),--(FM!J$3:J$5403<=$G$1)) .... where E1 and G1 are the date "limits", respectively :) Quite happy to have this finished! Hopefully this can help someone in the future. |
Counting dates in a RANGE (yargh!) :)
Here's an alternative:
You only need to test for the year: =SUMPRODUCT(--(YEAR(FM!J$3:J$5403)=2004)) Biff "S Davis" wrote in message ups.com... Step three (counting dates between ranges) works by using: =SUMPRODUCT(--(FM!J$3:J$5403$E$1),--(FM!J$3:J$5403<=$G$1)) ... where E1 and G1 are the date "limits", respectively :) Quite happy to have this finished! Hopefully this can help someone in the future. |
All times are GMT +1. The time now is 10:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com