Excel sheets nearly store all types and sorts of data that we happen to interact with within our day-to-day life activities. In excel sheets you can record time, date, schedule events and activities, keep a record of items and their prices, and many more.
Any type of data we wish to record or record in Excel sheets is time-dependent even though we may not include tome in all the records, but dates will always remain a vital aspect.
We use dates in excel to keep a good track of the time when our records were created and recorded. With date also you can easily trace a particular set of data or a cell because you will sort the entire data by date.
To count all the cells that contain dates is just the way to give the numerical values of cells that have the data values
We can use the following procedure with examples to guide us in the activity of counting the number of cells with dates.
Using COUNTA Function
Come up with a data set where you will need to tally the cells with date values. Open a new excel sheet from your computing device and record your data in it as shown below
The next step in this procedure will be to count the number of cells with data values relating to the date in the given set of data. For us to count cells from a given data in excel, we use the function COUNTA. This is the all-counting function; it does all operations related to counting.
To count cells from a date in excel sheets, write the formula =COUNTA (B2: B6) on the formula bar and click on the enter button. B2 and B6 are the data range that we are working on. The commonly used data format in excel sheets is the month, day, and year format.
The result will appear on the result cell, cell D2 on the spreadsheet.
From the above scenario, the number of cells in the excel sheet with values related to dates is 5.
Using SUMPRODUCT Function to Count Dates in A Given Year
The method allows you to know dates in specific years when counting cells. It applies when the dataset has different dates in different years. Here is how to use the method, whose formula is
=SUMPRODUCT(–(YEAR($D$5:$D$12) =$F5))
1. Click on cell F5 and type the formula.
2. Press the Enter button. Use the mouse to drag and Autofill the formula in other cells.
When using this formula, the YEAR function extracts all the years from all the valid year cells and matches them with the year given in cell F5. Each date is then compared to the year value in the year column to get an array of TRUE and FALSE. Therefore, using Autofill changes the criteria value and the YEAR function.
Using a Combination of Functions
You can also use a combination of functions to count the number of dates in cells. In this case, you will use the formula;
=SUM (IF(ISERROR(DATEVALUE (TEXT(D5:D12, "dd/MM/yyyy"))), 0,1))
1. Click on cell F5 and write the formula.
2. Press CTRL+SHIFT+ENTER buttons altogether. If you are using Excel 365, you can only press the Enter button, and you will see the results.
Here, whether the cells have numbers values or not, the ISERROR function will give the FALSE result if the cell is not blank and TRUE if the cell is blank. On the other hand, the IF function will SUM 1 for each FALSE value and 0 for the TRUE value.
Using COUNTIFS Function to Count Dates in the Current Month
The COUNTIFS function allows you to count dates in the current and previous months. For example, if you have a dataset that shows the joining dates, you may want to know how many joining dates are in the current or previous month. In this case, you will use the formula:
=COUNTIFS (D5:D12,">="&EOMONTH(TODAY (), -1)+1,D5:D12,"<"&EOMONTH(TODAY(),0)+1)
1. Click on cell G5 and write the formula.
2. Press the Enter button.
3. The step will give the total dates in the current month. To get the joining dates in the previous month, proceed as follows.
4. Click on cell H5 and type this formula:
=COUNTIFS(D5:D12,">="&EOMONTH(TODAY(),-2)+1,D5:D12,"<"&EOMONTH(TODAY(),-1)+1)
- Press the Enter button, and you get the final result.
The formula depends on the COUNTIF function to count dates greater than or equal to the current month’s first day and less than the next month’s first day. It creates both dates using the EOMONTH function, which takes the current date from the TODAY function.
Using VBA
Another approach to counting numbers of cells with dates in Excel is using VBA. To do this, follow these steps:
1. Right-click on the worksheet and go to View Code.
Alternatively, Go to the Developer tab and then Visual Basic
2. You can also open the Microsoft Visual Basic for Applications window by pressing Alt + F11 keys altogether.
3. Click the Insert Tab, select Module, and paste the VBA code below in the Module window.
Option Explicit
Function DateCells(dRanges As Range) As Variant
Dim darr() As Variant
Dim drng As Range
Dim dcount As Integer
Application.Volatile
ReDim darr(dRanges.Cells.Count - 1) As Variant
dcount = 0
For Each drng In dRanges
darr(dcount) = VarType(drng)
dcount = dcount + 1
Next
DateCells = darr
End Function
4. Press the F5 or play button to run the code.
5. You can now enter the formula below in cell F5.
=SUM(IF(DateCells(D5:D12)=7,1,0)), where;
D5:D15 is the specific range you will need to count the number of cells containing dates. You can change it based on values in your worksheet.
6. Finish by pressing the CTRL + SHIFT + ENTER buttons altogether.
The VBA code will create a new user function called DateCells, which checks data values in a given array or ranges and SUM them individually if the date value is valid.
Using Kutools for Excel to Count Cells That Contain Any Dates in A Specified Range
This method applies only if you have already-installed Kutools for Excel. You can use its Select Nonblank Cells utility to count and select cells that contain any data quickly as follows:
1. Select the range of cells you want to count if they contain any data.
2. Click on Kutools > Select > Select Nonblank Cells.
3. The step will select all cells with data in a specific range. A dialog box will also show how many nonblank cells you selected.
4. Click the OK button to close the dialog box.
