Count dates if less than specific date
To count the number of dates that are less than another date we can apply an Excel or VBA method
Example: Count dates if less than specific date
|
=COUNTIF(B8:B12,"<"&C5)
|
The formula uses the Excel COUNTIF function to count the number of cells in range (B8:B12) that have a date less than the date in cell C5.
|
Sub Count_dates_if_less_than_specific_date()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to count the number of dates that are less than the date in cell (C5)
ws.Range("E8") = Application.WorksheetFunction.CountIf(ws.Range("B8:B12"), "<" & ws.Range("C5"))
ws.Range("E8") = Application.WorksheetFunction.CountIf(ws.Range("B8:B12"), "<" & ws.Range("C5"))
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Data Range: Ensure that the date that you want to count from are captured in range ("B8:B12") in the Analysis worksheet.
Specific Value: Input the specific date in cell ("C5"), in the Analysis worksheet, that you want to count if the date in the specified range is less than this value.
Worksheet Name: Have a worksheet named Analysis.
Data Range: Ensure that the date that you want to count from are captured in range ("B8:B12") in the Analysis worksheet.
Specific Value: Input the specific date in cell ("C5"), in the Analysis worksheet, that you want to count if the date in the specified range is less than this value.
ADJUSTABLE PARAMETERS
Specific Value: Select the specific value, by changing the value in cell ("C5"), that you want to count if a date from a specified range is less than this value. Alternatively, you can replace ws.Range("C5") in the VBA code with the specific date or a defined name that represents the specific date that you want to test for.
Date Range: Select the date range that you want to count from by changing range ("B8:B12") to any range in the worksheet, that doesn't conflict with the formula.
Output Range: Select the output range by changing the cell reference ("E8") to any cell in the worksheet, that doesn't conflict with the formula.
EXPLANATION
To count the number of dates that are less than another date we can apply an Excel or VBA method. The formula used to count the number of dates that are less than another date is driven by an Excel COUNTIF function.
In both the VBA and Excel examples the formula counts the dates from a specified range (B8:B12) that contain a date less than 15/03/2017, which is the date captured in cell C5. This is achieved through the use of the Excel COUNTIF function.
To count the number of dates that are less than another date we can apply an Excel or VBA method. The formula used to count the number of dates that are less than another date is driven by an Excel COUNTIF function.
In both the VBA and Excel examples the formula counts the dates from a specified range (B8:B12) that contain a date less than 15/03/2017, which is the date captured in cell C5. This is achieved through the use of the Excel COUNTIF function.
FORMULA
=COUNTIF(range, "<"date)
=COUNTIF(range, "<"date)
ARGUMENTS
range: The range of cells that captures the dates that you want to count from.
date: The date that is used to determine which of the cells should be counted if the date, from a specified range, is less than this date.
range: The range of cells that captures the dates that you want to count from.
date: The date that is used to determine which of the cells should be counted if the date, from a specified range, is less than this date.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count dates if greater than specific date | How to count the number of dates that are greater than another date using an Excel and VBA method | |
| Count dates that equal to a specific date | How to count the number of dates that are equal to another date using an Excel and VBA method |