Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Count odd or even cells that have a date value

Copper Contributor

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.

excel.png

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

All help is really appreciated!

 

5 Replies

@Bourrinho 

 

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.

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.

excel 2.png

best response confirmed by Bourrinho (Copper Contributor)
Solution

@Bourrinho 

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)  )
Brilliant! This works also for the full file in my reply above.

Thanks!

@Bourrinho , you are welcome

1 best response

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

@Bourrinho 

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)  )

View solution in original post