SOLVED

# Count odd or even cells that have a date value

Copper Contributor

# Count odd or even cells that have a date value

Hello,

I would like to count the dates in a column but only in the odd or even cells.

More details (see also image) : it's a column having predicted and actual dates. Only the cells that have a completed actual date should be counted. However some dates can be NA or cancelled, so it's not possible to just count all "actual date" cells with content. In the image below the actuals reached is manually counted for (it's also a small example) but in a bigger file would be useful to have it counted once a date is added "under actual" for that specific task. Ideally the formula doesn't reference to "Actual" as in column B below since those labels are not always present.

I tried options with MOD(ROW) and COUNTIFS but didn't get it to work.

All help is really appreciated!

5 Replies

# Re: Count odd or even cells that have a date value

Hi,

Is your date always in "dd/mm/yyyy" format? if so,  you can use =ISNUMBER(DATEVALUE(TEXT(B2,"dd/mm/yyyy"))) to check whether the content of the cell is a date or not first. and then use COUNTIFS.

# Re: Count odd or even cells that have a date value

Hi @rachel

that could work if the file was small like above. However I picked a simple example to not make my explanation sound to difficult and just search for the formula. The actual file looks like below. E.g. Students having to complete a certain task within a certain window based upon their start date. To count the number of subjects starting, I could easily do this by counting the cells that have a start date (COUNTA). For the "Tasks", I would like to have a count each time a completion date was added. So in the example below, I would like to have that Task 1 shows 2 completions on top as 2 completion dates were added for the participating students.

best response confirmed by Bourrinho (Copper Contributor)
Solution

# Re: Count odd or even cells that have a date value

As variant

``````=SUMPRODUCT( ISEVEN(ROW(\$B\$2:\$B\$13 ))*ISNUMBER(\$B\$2:\$B\$13)  )
=SUMPRODUCT( ISODD(ROW(\$B\$2:\$B\$13 ))*ISNUMBER(\$B\$2:\$B\$13)  )``````

# Re: Count odd or even cells that have a date value

Brilliant! This works also for the full file in my reply above.

Thanks!

# Re: Count odd or even cells that have a date value

@Bourrinho , you are welcome

1 best response

Accepted Solutions
best response confirmed by Bourrinho (Copper Contributor)
Solution

# Re: Count odd or even cells that have a date value

As variant

``````=SUMPRODUCT( ISEVEN(ROW(\$B\$2:\$B\$13 ))*ISNUMBER(\$B\$2:\$B\$13)  )
=SUMPRODUCT( ISODD(ROW(\$B\$2:\$B\$13 ))*ISNUMBER(\$B\$2:\$B\$13)  )``````