In many situations, we need to count the cases that fall in a year based on the start and end dates. For example, there are many contracts in a company that start and end on different dates.
You could only have the following possibilities for all the contracts in 2015 when considering the start and end dates.
- Start after Dec 31, not count in;
- Start between Jan 1 and Dec 31, and ends after Dec 31, count in;
- Start between Jan 1 and Dec 31, and ends before Dec 31, count in;
- Start before Jan 1 and ends between Jan 1 and Dec 31, count in;
- Start before Jan 1 and ends after Dec 31, count in;
- Start before Jan 1 and ends before Jan 1, not count in.
To be able to count all the contracts that ever exist in a year from Jan 1, 2015 to Dec 31, 2015 based on the start and end dates, we need to get the 4 possibilities with green lines.
Please use the following formula and change the start and end dates to your own.
=IF(OR(AND(A2>=DATEVALUE("2015/01/01"), A2<=DATEVALUE("2015/12/31")), AND(B2>=DATEVALUE("2015/01/01"), B2<=DATEVALUE("2015/12/31")), AND(A2<=DATEVALUE("2015/01/01"), B2>=DATEVALUE("2015/12/31"))), "2015", "")
Where A2 is the Start date and B2 is the End Date;
- "AND(A2>=DATEVALUE("2015/01/01"), A2<=DATEVALUE("2015/12/31"))" is to figure out that the start dates are from 2015-01-01 to 2015-12-31;
- "AND(B2>=DATEVALUE("2015/01/01"), B2<=DATEVALUE("2015/12/31"))" is to figure out that the end dates are from 2015-01-01 to 2015-12-31;
- "AND(A2<datevalue("2015 01="" 01"),="" b2="">DATEVALUE("2015/12/31"))</datevalue("2015>" is to figure out those started before 2015-01-01 and finished after 2015-12-31.
This formula works but it is too long. If you watch the drawing above carefully, you will find all the contracts in a year (or any period) have their start dates on or before December 31, 2015, and all the end dates are on or after January 1, 2015. The following formula will also do the work but it is way short.
=IF(AND(A2<=DATEVALUE("2015/12/31"),B2>=DATEVALUE("2015/01/01")),"2015","")
Where A2 is the cell with the start date and B2 is the cell with the end date.
After you label each contract, you can easily count the total by using the COUNTIF function. In the example above, the count of contracts in 2015:
=COUNTIF(D2:D7,"2015")
The result will return to 4.