Excel公式:计算值为日期的单元格 [英] Excel Formula: Count cells where value is date

问题描述

我正在寻找一个公式来在一定范围的单元格上运行COUNTIF(或类似值)，并且其中包含的值是日期，以增加计数器-本质上是这样的:

I'm looking for a formula to run a COUNTIF (or similar) on a range of cells, and where the contained value is a date, to increment the counter - essentially something like:

=COUNTIF(range, if_date())

我找不到的是对该问题的if_date()部分进行的逻辑测试.有没有一种方法可以测试单元格以检查其内容是否为日期?

What I haven't been able to find is a logical test for the if_date() part of the question. Is there a way to test a cell to check whether its content is a date?

推荐答案

使用工作表函数很难做到这一点，因为excel中的日期只是简单格式化的数字-只有CELL函数允许您调查单元格的格式(并且您可以t将其应用于范围，因此将需要一个辅助列).......或者，如果您只有日期和空格.....或日期和文本，那么使用COUNT就足够了功能，即

This is difficult with worksheet functions because dates in excel are simply formatted numbers - only CELL function lets you investigate the format of a cell (and you can't apply that to a range, so a helper column would be required).......or, if you only have dates and blanks.....or dates and text then it would be sufficient to use COUNT function, i.e.

=COUNT(range)

这是对数字进行计数，因此如果您想将日期与数字区分开来，那将是不够的.如果您这样做，则可以利用数字范围，例如如果您的数字在某个范围和日期内，但所有数字均小于10,000，并且所有日期都相对较新，则可以使用此版本排除数字

That counts numbers so it won't be adequate if you want to distinguish dates from numbers. If you do then the number range could be utilised, e.g. if you have numbers in a range and dates but the numbers will all be lower than 10,000 and the dates will all be relatively recent then you could use this version to exclude the numbers

=COUNTIF(range,">10000")

这篇关于Excel公式:计算值为日期的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！