Number of Dates in a Date column.
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.
Best Answer
-
You can use the
COUNTIF
function combined with theISDATE
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 ofTRUE
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.
- Add a helper column that checks if the cell contains a date using the
Answers
-
You can use the
COUNTIF
function combined with theISDATE
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 ofTRUE
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.
- Add a helper column that checks if the cell contains a date using the
-
@EvanShortreed fantastic, many many thanks.
-
Ed,
Glad I could help, let us know if you ever need anything else from Entheos.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!