1

I have a column with datetimes of different days with different times and I want to know how many different days are in that column. I got it to work in OpenOffice on an English system but it does not work anymore on an Italian or German system with Microsoft Excel:

=SUMPRODUCT((IF(FREQUENCY(DATEVALUE(MONTH(A1:A9)&"/"&DAY(A1:A9)&"/"&YEAR(A1:A9)),DATEVALUE(MONTH(A1:A9)&"/"&DAY(A1:A9)&"/"&YEAR(A1:A9))),TRUE())>0)*(MONTH(A1:A9)=6))

And that's what my column looks like

2016-06-29 19:50:00
2016-06-29 20:00:00
2016-06-29 11:34:00
2016-06-29 11:34:24
2016-06-29 12:33:29
2016-06-29 20:47:00
2016-06-30 11:46:46
2016-07-01 13:11:55
2016-07-01 13:12:26

For these values I would expect the formula to return 3, because there are 3 different days.

Finally I would like to amend the formula so that I can filter it by month - so that I can say: 'give me the number of distinct dates in june' and it returns 2.

1

I could not get your formula to work in Excel English.

This formula should work in excel, (you may need to translate to the correct language):

=SUM(--(FREQUENCY(IF(MONTH(A1:A9)=6,MATCH(INT($A$1:$A$9),INT($A$1:$A$9),0)),ROW($A$1:$A$9)-ROW($A$1)+1)>0))

It is an array formula and needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting Edit mode. If done correctly then Excel will put {} around the formula.

enter image description here

1

Here is a simple way for you to determine the count for a certain condition. I put your example into a workbook and wrote a formula to count how many occurrences with the month of 6 in them:

enter image description here

The formula I used, uses an array reference which is why it has the curly brackets around it. Once you enter the formula in, press Shift + Enter instead of just Enter by itself.

The if statement says that if the month = 6 then return 1, and if not 0. Then it simply adds up the totals with the sum function. If you wanted to change it to look for year, you would simply change out the MONTH() formula part.

Also note how much cleaner and simple my formula is, which is much easier to follow

  • SUMPRODUCT((MONTH($A$1:$A$9)=6)*1) also works without the need to CSE. Also a quick note: Because Array formulas calculations are exponential, full column references should be avoided. The formula will continue the checking every cell in the column and add 0 to the array that get passed to the SUM formula. It will not have a huge effect on this one formula, but fill a few hundred and it will cause a lag and possible memory overflow. – Scott Craner Sep 9 '16 at 15:49
  • Sure I understand the performance risks but I wanted to keep the example simple so that the OP can understand the concept at least. – Eric F Sep 9 '16 at 15:53
  • Will that work with German and Italian setting? – Máté Juhász Sep 9 '16 at 16:03
  • @MátéJuhász Yep. – Eric F Sep 9 '16 at 16:03
  • Though I think the OP want the answer of 2 which is the number of unique days in the 6th month. I want to know how many different days are in that column. – Scott Craner Sep 9 '16 at 16:06
1

I used some helper column to sove As in upper figure your data is in A1 to A9

in B1 use

=DATE(YEAR(A1),MONTH(A1),DAY(A1))

This will convert data into date (removing time)

in C1 use this

=IF(COUNTIF($B$1:B1,B1)>1,2,1)

This will put 1 on the first occurrence of a date and 2 for other

Finally you can count 1's by using

=COUNTIF(C1:C9,1)

Regards

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.