Date range within date range...

[weissan]

Ok, this is probably simple, but I can't ge my head around it...

I have two columns (A & B) containing start and end dates for contracts (som 1 week long, others several years long). What I want to do is create a formula that tells me how many contracts have been active (regardless of whether the contract has been active for just 1 day or 365 days) during each and every relevant calendar year. Basically this is statistics for HR workload.

I've created two additional columns with the start and end dates of each relevant calendar year and I've tried to use COUNTIF and COUNTIFS with these... but to no avail.

Help!

W

 

[weissan]

*banging my head agains the wall*

 

[AlphaFrog]

Excel Workbook
	A	B	C	D	E
1	Contract Start	Contract End		Year	Count
2	6/23/2011	6/24/2011		2010	0
3	6/23/2012	6/24/2012		2011	1
4	6/23/2012	6/23/2015		2012	2
5	6/23/2013	6/24/2013		2013	2
Sheet

 

[weissan]

Ah, brilliant! Thanks!!!

By now I've already run into the next problem...

I have a list of 3000 names in one column and most names some appear multiple times. I need a way to count the number of unique individuals on the list... suggestions?

Also - but this get's pretty **** complicated - is there a way to get Excel to list every unique text entry in a range and how many times it appears? I have a list of 3000 entries of countries (including misspelled versions, old country names, i.e. Soviet Union). These will later on be made more coherent in the database from where I've export the data, but for now I'd like to get a complete list of all unique entries + how many times that entry appears... Hmmmm...