Excel Formula Count cells where value is date

Question

I m looking for a formula to run a COUNTIF  or similar  on a range of cells  and where the contained value is a date  to increment the counter - essentially something like    COUNTIF range  if date      What I haven t been able to find is a logical test for the if date   part of the question  Is there a way to test a cell to check whether its content is a date

User · Answer

To count numbers or dates that meet a single test (such as equal to, greater than, less than, greater than or equal to, or less than or equal to), use the COUNTIF function. In Excel 2007 and later, to count numbers or dates that fall within a range (such as greater than 9000 and at the same time less than 22500), you can use the COUNTIFS function. If you are using Excel 2003 or earlier, you can use the SUMPRODUCT function to count the numbers that fall within a range (COUNTIFS was introduced in Excel 2007).

please see more

User · Answer

A bit long winded but it works for me  try this      SUM IF OR ISBLANK AU2   NOT ISERR YEAR AU2     0 1    IF OR ISBLANK AV2   NOT ISERR YEAR AV2     0 1     first part of if will allow cell to be blank or if there is something in the cell it tries to convert to a year  if there is an error or there is something other than a date result   1  do the same for each cell and sum the result

User · Answer

There is no interactive solution in Excel because some functions are not vector-friendly  like CELL  above quoted  For example  it s possible counting all the numbers whose absolute value is less than 3  because ABS is accepted inside a formula array   So I ve used the following array formula   Ctrl Shift Enter after edit with no curly brackets                  SUM IF ABS F1 F15  lt 3 1 0      If Column F has                     F           1        2           2        4           3       -2           4        1           5        5   It counts 3   -2 2 and 1   In order to show how ABS is array-friendly function let s do a simple test  Select G1 G5  digit  ABS F1 F5  in the formula bar and press Ctrl Shift Enter  It s like someone write Abs F1 F5  1   Abs F1 F5  2   etc                      F    G           1        2   ABS F1 F5    gt  2            2        4   ABS F1 F5    gt  4           3       -2   ABS F1 F5    gt  2           4        1   ABS F1 F5    gt  1           5        5   ABS F1 F5    gt  5   Now I put some mixed data  including 2 date values                      F           1        Fab-25-2012           2        4           3        May-5-2013           4        Ball           5        5   In this case  CELL fails and return 1                 SUM IF CELL  format  F1 F15   D4  1 0      It happens because CELL return the format of first cell of the range   D4 is a m-d-y format   So the only thing left is programming  A UDF User defined Function  for formula array must return a variant array   Function TypeCell R As Range  As Variant Dim V   As Variant Dim Cel As Range Dim I As Integer Application Volatile     For revaluation in interactive environment ReDim V R Cells Count - 1  As Variant I   0 For Each Cel In R    V I    VarType Cel      Output array has the same size of input range     I   I   1 Next Cel TypeCell   V End Function   Now is easy  the constant VbDate is 7                  SUM IF TypeCell F1 F5  7 1 0       It shows 2  That technique can be used for any shape of cells   I ve tested vertical  horizontal and rectangular shapes  since you fill using for each order inside the function

User · Answer

This assumes that the column of potential date values is in column A   You could do something like this in an adjacent column    Make a nested formula that converts the  date  to its numeric value if it s valid  or an error value to zero if it s not  Then it converts the valid numeric values to 1 s and leaves the zeroes as they are  Then sum the new column to get the total number of valid dates    IF IFERROR DATEVALUE A1  0  0 1 0

User · Answer

Here is how I was able to trick Excel to count expired certifications in a list  I didn t have a set date  or date range  just current date   TODAY    doesn t work in these for Excel 2013  It sees it as text or condition  not the date value  So these previous didn t work for me  So the word problem scenario  How many people are expired in this list   Use   IFERROR D5-TODAY   0  Where D5 is the date to be interrogated    Then use   IF J5  1 1 0  Where J5 is the cell where the first equation is producing either a positive or negative number   This set  I have hidden on the side of the visible sheet  then I just sum the total for the number of unexpired members

User · Answer

Total number of cells in a range minus the blank cells of the same range     115 -  COUNTBLANK C2 C116     This counts everything in the range so  maybe not what you re looking for

User · Answer

Here s my solution   If your cells will contain only dates or blanks  just compare it to another date   If the cell can be converted to date  it will be counted    COUNTIF C C   gt 1 1 1900      Counts all the dates in column C   Caution  cells with numbers will be counted

User · Answer

This is difficult with worksheet functions because dates in excel are simply formatted numbers - only CELL function lets you investigate the format of a cell  and you can t apply that to a range  so a helper column would be required        or  if you only have dates and blanks     or dates and text then it would be sufficient to use COUNT function  i e    COUNT range   That counts numbers so it won t be adequate if you want to distinguish dates from numbers  If you do then the number range could be utilised  e g  if you have numbers in a range and dates but the numbers will all be lower than 10 000 and the dates will all be relatively recent then you could use this version to exclude the numbers   COUNTIF range   gt 10000

User · Answer

Here s one approach   Using a combination of the answers above do the following     Convert cell to text using a predefined format Try using DATEVALUE to convert it back to a date Exclude any cells where DATEVALUE returns an error   As a formula  just use the example below with  lt   replaced with your range reference    SUM IF ISERROR DATEVALUE TEXT  lt  lt RANGE HERE gt  gt    MM dd yyyy      0  1     You must enter this as an array formula with CTRL   SHIFT   ENTER

[excel] Excel Formula: Count cells where value is date

Examples related to excel

Examples related to excel-formula