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.
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")
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
- Difference Between SUMIF and COUNTIF Functions in Excel
- How to Compare Two Columns Using COUNTIF Function
- COUNTIF Function to Count Cells That Are Not Equal to Zero
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.
- 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)
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
- How to Use COUNTIF for Non Contiguous Range in Excel
- Excel COUNTIF to Count Cell That Contains Text from Another Cell
- How to Use COUNTIF Function to Count Text from List in Excel
- Count Text at Start with COUNTIF & LEFT Functions in Excel
- How to Use COUNTIF Function In Excel to Count Bold Cells
- How to Use COUNTIF Function to Calculate Percentage in Excel
- How to Use COUNTIF Function in Excel Greater Than Percentage
- How to Use COUNTIF Function with Array Criteria in Excel
- How to Use SUMIF, COUNTIF and AVERAGEIF Functions in Excel
- How to Use the Combination of COUNTIF and SUMIF in Excel
- How to Use IF and COUNTIF Functions Together in Excel
- How to Use Nested COUNTIF Function in Excel
- How to Use COUNTIF and COUNTA Functions Together in Excel
- How to Calculate Frequency Using COUNTIF Function in Excel
- Excel COUNTIF Function with Conditional Formatting
- [Solved!]: Excel COUNTIF Returning 0 Instead of Actual Value
- [Fixed] COUNTIF Function with Wildcard Not Working in Excel
- [Fixed!] Excel COUNTIF Function Not Working for String “True”