Counting the number of times a date is entered but ignoring the time

Question

In Excel my date formats in a single column look like this: 1/29/2025 4:04:02 PM

The same date with different times is entered multiple times in the column.

What I would like to do is have a cell that displays how many times a single date has been entered.

In testing, if I change the format to be just the date without the time it counts properly so I think I'm close to an answer with this: =SUM(IF(FREQUENCY(B3:B99,B3:B99)>0,1))

If I have 10 entries for 1/29/2025 but all with different times it will give me an answer of 10, I believe because the times are unique.

I want an answer of 1 because they were all entered on the same day. I think I need to find a way to ignore the time and just calculate off the date.

Any advice would be greatly appreciate.

Be sure to include some samples and expected results. If you would expect 1 for each date that resembles another listed date, what would you expect for unique date values? Note that INT() trims off the time. — P.b
– P.b, Commented Mar 8 at 19:39
Create a column C that looks at column B thus =TRUNC(B3,"D") , and then run your SUM Frequency over column C. — Mike G
– Mike G, Commented Mar 10 at 12:10

rotabor · Accepted Answer · 2025-03-08 18:41:11Z

3

The solution for the request "What I would like to do is have a cell that displays how many times a single date has been entered." using Microsoft-365 is very simple:

=GROUPBY(INT(A2:A5),A2:A5,COUNTA)

Or may be do you need the number of unique dates considering "I want an answer of 1 because they were all entered on the same day"?

=COUNT(UNIQUE(INT(A2:A5)))

edited Mar 8 at 18:41

answered Mar 8 at 18:33

rotabor

5,6112 gold badges5 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brian H Mar 8 at 20:36

Wahoo!! Thank you very much for you quick reply and resolution to my issue. It was this one that did the trick. I just needed to know total unique days.: =COUNT(UNIQUE(INT(A2:A5))) Thanks again, I really appreciate the help

Collectives™ on Stack Overflow

Counting the number of times a date is entered but ignoring the time

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related