Count with time part?

[Fredep57]

OK, I have a sheet. It has multiple columns but I need to find all the "unique" values in the date/time column. However the date is formatted as "yyyy-mm-dd hh:mm:ss.000" and because of logging, the data comes in from multiple places and could be off by a couple of seconds.

So, how can I count the unique times (meaning by "yyyy-mm-dd hh:mm") only? Stumped so far as I used the forumula in the cell "=SUMPRODUCT((O25:O41<>"")/COUNTIF(O25:O41,O25:O41&""))" and that is OK but is too granular for what I am trying to accomplish.

Thanks
F

 

[Aladin Akyurek]

OK, I have a sheet. It has multiple columns but I need to find all the "unique" values in the date/time column. However the date is formatted as "yyyy-mm-dd hh:mm:ss.000" and because of logging, the data comes in from multiple places and could be off by a couple of seconds.

So, how can I count the unique times (meaning by "yyyy-mm-dd hh:mm") only? Stumped so far as I used the forumula in the cell "=SUMPRODUCT((O25:O41<>"")/COUNTIF(O25:O41,O25:O41&""))" and that is OK but is too granular for what I am trying to accomplish.

Thanks
F

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A1:A5<>"",MATCH(TEXT(A1:A5,"yyyy-mm-dd hh:mm"),
     TEXT(A1:A5,"yyyy-mm-dd hh:mm"),0)),ROW(A1:A5)-ROW(A1)+1),1))