excel - Counting the number of times a date is entered but ignoring the time - Stack Overflow

admin管理员组

文章数量:1405328

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.

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.

• excel
• datetime
• excel-formula

Share Improve this question Follow edited Mar 8 at 17:50 Brian H asked Mar 8 at 17:45 Brian HBrian H 111 bronze badge 2

• 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 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 Commented Mar 10 at 12:10

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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)))

本文标签： excelCounting the number of times a date is entered but ignoring the timeStack Overflow