Count cells between dates

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.

Formula

=COUNTIFS(range,">="&date1,range,"<="&date2)

Used as

=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.

Example 1

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.

  1.   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

  1. Prepare your birthday-count table

Figure 2 – Showing the birthday-count table containing a list of years

  1. 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

  1. Slot in your ‘COUNTIFS’ formula

=COUNTIFS(dates,”>=”&DATE(D3,1,1), dates,”<=”&DATE(D6,12,31))

Figure 4 – Showing the format with which the formula should be written

  1. View Result of the Birthday Count

Figure 5 – Showing the result of birthday count from 1991 to 1997

Note

  •      “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.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar