Count Cells between Dates
Excel makes it possible to count the number of cells that are available between two dates. If the need ever arises to count how many times a set of dates appear in a superset of dates, the COUNTIFS function should be used.
Using Excel to compute this information is especially useful for a large data set.
=COUNTIFS (B3 : B10 ,”>=”&DATE (D3,1,1), B3 : B10,”<=”&DATE (D6,12, 31))
How to Calculate Cells between Dates
The formula for calculating the cells between date is quite straightforward and will be further understood as soon as the parameters are explained.
- Range: This refers to the named range i.e. the column containing the data to be filtered e.g. B2:B18.
- Date: date1 represents the Date function that accepts three parameters – the cell in question – which contains the year, then the other two containing the month and the day.
It will look like this ‘& Date’ (B2, 1, 1).
This represents the 1st month and the 1st day of the year – the earlier date
- COUNTIFS allows for multiple criteria hence the date1, date2 seen in the formula.
date2 will contain the information for the end of the year or the end of a period in the year. E.g. & Date (B2, 12,31) – the later date.
- The >= and <= signs in the formula must be used in quotes so as to be read as text, and the ampersand (&) sign is used in the formula to concatenate the logical operators to the Date function.
Let us assume that you have a list of students and their dates of birth, but you need to figure out how many of them are born in each year.
The following steps should be taken.
- Paste the list of students showing their names and date of birth.
Figure 1 – Showing the list of students with their names and date of birth
- Prepare your birthday-count table
Figure 2 – Showing the birthday-count table containing a list of years
- Declare the range; in this case, cell B2 to cell B:10 – 1991 to 1997
Figure 3 – Showing the range of dates of birth to be tested using the COUNTIFS formula
- Slot in your ‘COUNTIFS’ formula
Figure 4 – Showing the format with which the formula should be written
- View Result of the Birthday Count
Figure 5 – Showing the result of birthday count from 1991 to 1997
- “dates=B3:B10” as seen in the illustration is called a named range, and in this context, it refers to a range of birth dates
- It is important to note that the part of the formula – dates – must be replaced by a named range e.g. B3:B10 as seen in the example above.
If not, the result will not be computed.
- When calculating cells between dates, the Date function should contain 3 parameters.
As earlier stated, it must contain the early date i.e. the date when the analysis should start and, the later date, where the analysis should stop.
In the example above, generic parameters were used for the date function – DATE(D3,1,1) and DATE(D3,12,31)
- The first parameters of the Date functions change along the cells.
On D4, the formula contains DATE(D4,1,1) and DATE(D4,12,31)
On D5, the formula contains DATE(D5,1,1) and DATE(D5,12,31)
This process is repeated until the Birthday count column is exhausted.