How to Use COUNTIF to Count Date Less Than Today in Excel

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for Excel countif date less than today, you have come to the right place. Here, we will walk you through 3 easy and effective methods to do the task smoothly


How to Use COUNTIF Function to Count Date Less Than Today in Excel: 3 Methods

In the following dataset, there are Product Name and Invoice Date columns. Using this dataset, we will go through 3 easy methods to countif date less than today.

Here, we used Microsoft Excel 365. You can use any available Excel version. Let’s go through the steps.

Dataset for Excel Countif Date Less Than Today


1. Using Today’s Date in COUNTIF Function

In this method, we will use today’s date in the COUNTIF function so that we can countif the date is less than today.

Here, in cell C12, you can see Today’s Date.

Steps:

  • First of all, to find out the countif date less than today’s date, we will type the following formula in cell C13.
=COUNTIF($C$5:$C$10,"<3-Nov-22")

Using COUNTIF Function for Excel Countif Date Less Than Today

Formula Breakdown

  • COUNTIF($C$5:$C$10,”<3-Nov-22″) → the COUNTIF function calculates the number of dates based on a criterion in a range of cells.
  • $C$5:$C$10→ is the range of cells.
  • <3-Nov-22 → is the criterion.
  • COUNTIF($C$5:$C$10,”<3-Nov-22″) → becomes
    • Output: 5
  • Explanation: 5 is the number of dates less than today’s date.
  • After that, press ENTER.

As a result, you can see the number of dates less than today in cell C13.

Read More: How to Use Excel COUNTIF Between Time Range


2. Use of TODAY within COUNTIF Function

In this method, we will use the TODAY function as the criterion of the COUNTIF function.

Steps:

  • In the beginning, we will type the following formula in cell C13.
=COUNTIF($C$5:$C$10,"<"&TODAY())

Formula breakdown

  • TODAY() → the TODAY function returns the current day date.
  • TODAY() → becomes
    • Output: 03-Nov-22
  • COUNTIF($C$5:$C$10,”<“&TODAY()) → returns the number of dates less than today.
    • Output: 5
  • Explanation: 5 is the number of dates less than today.
  •  Afterward, press ENTER.

Therefore, you can see the number of dates less than today in cell C13.


Similar Readings


3. Employing Cell Reference with COUNTIF Function

In this method, we will use the TODAY function to find out today’s date. And after that, we will use that date as a criterion in the COUNTIF function.

Steps:

  • To begin with, we will type the following formula in cell  C12.
=TODAY()

Here, the TODAY function returns the current day date.

Applying TODAY Function to Excel Countif Date Less Than Today

  • After that, press ENTER.

As a result, you can see Today’s Date in cell C12.

Next, we will use C12 as a cell reference for the COUNTIF function.

  • Afterward, we will type the following formula in cell C13.
=COUNTIF($C$5:$C$10,"<"&$C$12)

Using Cell Reference for Excel Countif Date Less Than Today

Formula Breakdown

  • COUNTIF($C$5:$C$10,”<“&$C$12)→ Here, cell C12 acts as a criterion for the COUNTIF
    • Output: 5
  • Explanation: 5 is the number of days less than today’s date.
  • Moreover, press ENTER.

Then, you can see the result in cell C13.


How to Use  COUNTIF to Count Date Greater Than 30 Days in Excel

Here, we will describe how you can count dates greater than 30 days.

Steps:

  • First of all, we will type the following formula in cell C13.
=COUNTIF($C$5:$C$10,">"&TODAY()-30)

Formula breakdown

  • TODAY() → the TODAY function returns the current day date.
    • Output: 03-Nov-22
  • TODAY()-30 → returns the date that is 30 days less than the current day date.
    • Output: 04-Oct-22
  • $C$5:$C$10→ is the range of cells
  • COUNTIF($C$5:$C$10,”>”&TODAY()-30) → returns the date that is 30 days greater than the present day date.
    • Output: 2
  • Explanation: 2 is the number of dates that are 30 days greater than present day date.
  • At this point, press ENTER.
    As a result, you can see the dates greater than 30 days in cell C13.

Read More: Excel COUNTIF to Count Cells Greater Than 1


Practice Section

You can download the above Excel file to practice the explained method.


Download Workbook

you can download the excel file and practice while reading this article.


Conclusion

Here, we tried to show you 3 methods to use COUNTIF to get date less than today. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below.


Related Articles

Afia Kona
Afia Kona

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo