Number of Dates in a Date column.

Options
✭✭

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.

Options

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

1. Add a helper column that checks if the cell contains a date using the `ISDATE` function.
2. 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.

Options

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

1. Add a helper column that checks if the cell contains a date using the `ISDATE` function.
2. 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.

• ✭✭
Options

@EvanShortreed fantastic, many many thanks.

• Options

Ed,

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!