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
Download Workbook
you can download the excel file and practice while reading this article.
3 Methods to Use COUNIF Function to Count Date Less Than Today in Excel
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: COUNTIF Date Is within 7 Days
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.
Read More: COUNTIF Between Two Dates in Excel (4 Suitable Examples)
Similar Readings
- Difference Between SUMIF and COUNTIF Functions in Excel
- How to Compare Two Columns Using COUNTIF Function (4 Ways)
- Excel COUNTIF with Greater Than and Less Than Criteria
- How to Apply COUNTIF Between Two Cell Values in Excel
- Use Excel COUNTIF Function to Count Cells Greater Than 0
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.
Read More: How to Use COUNTIF for Date Range in Excel (6 Suitable Approaches)
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: COUNTIF Excel Example (22 Examples)
Practice Section
You can download the above Excel file to practice the explained method.
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. Please visit our website Exceldemy to explore more.
Related Articles
- How to Calculate Frequency Using COUNTIF Function in Excel
- Excel COUNTIF to Count Cell That Contains Text from Another Cell
- How to Count If Cells Contains Text from List in Excel
- Use Nested IF and COUNTIF Formula in Excel
- COUNTIF Function to Count Cells That Are Not Equal to Zero
- How to Use Nested COUNTIF Function in Excel (6 Suitable Ways)
- Apply Excel COUNTIF with Pivot Table Calculated Field