Number of Dates in a Date column.

Ed Gadd

I'm sure this will be a simple formula, I just cant think of it!!

I've a date column which contains dates. If a date is not required "N/A" is populated in the cell.

I'm looking for a formula to count the number of cells in the column which contains a date.

I don't need a range or specific month year ect. Just a formula to count the number of cells which have dates.

I'm sure this has been asked many times already, thanks in advance.

Find more posts tagged with

Accepted answers

EvanShortreed

You can use the COUNTIF function combined with the ISDATE function. Here's how you can do it:

Add a helper column that checks if the cell contains a date using the ISDATE function.
Use the COUNTIF function to count the number of TRUE values in the helper column.

Assume your dates are in Column A, and you want to count the number of cells with dates in Column A.

Step 1: Create a Helper Column

Name your helper column "Is Date".

In the first cell of the "Is Date" column (let's say it's B1), enter the formula:

=IF(ISDATE([Column A]1), 1, 0)

This formula will return 1 if the cell in Column A is a date, otherwise, it will return 0.

Step 2: Count the Dates

In the cell where you want to display the count of dates, enter the formula:

=SUM([Is Date]:[Is Date])

This will sum up all the 1s in the "Is Date" column, effectively giving you the count of cells that contain dates in Column A.

Example

Assuming Column A contains dates and Column B is your helper column "Is Date":

Column A	Is Date
01/01/2023	1
Text	0
02/15/2023	1
100	0
03/30/2023	1

Then, the formula =SUM([Is Date]:[Is Date]) will return 3, as there are three dates in Column A.

This method ensures you accurately count the number of cells containing dates.

All comments

EvanShortreed

You can use the COUNTIF function combined with the ISDATE function. Here's how you can do it:

Add a helper column that checks if the cell contains a date using the ISDATE function.
Use the COUNTIF function to count the number of TRUE values in the helper column.

Assume your dates are in Column A, and you want to count the number of cells with dates in Column A.

Step 1: Create a Helper Column

Name your helper column "Is Date".

In the first cell of the "Is Date" column (let's say it's B1), enter the formula:

=IF(ISDATE([Column A]1), 1, 0)

This formula will return 1 if the cell in Column A is a date, otherwise, it will return 0.

Step 2: Count the Dates

In the cell where you want to display the count of dates, enter the formula:

=SUM([Is Date]:[Is Date])

This will sum up all the 1s in the "Is Date" column, effectively giving you the count of cells that contain dates in Column A.

Example

Assuming Column A contains dates and Column B is your helper column "Is Date":

Column A	Is Date
01/01/2023	1
Text	0
02/15/2023	1
100	0
03/30/2023	1

Then, the formula =SUM([Is Date]:[Is Date]) will return 3, as there are three dates in Column A.

This method ensures you accurately count the number of cells containing dates.

Ed Gadd

@EvanShortreed fantastic, many many thanks.

EvanShortreed

Ed,

Glad I could help, let us know if you ever need anything else from Entheos.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.