gpt4 book ai didi

Google sheets function to get dates from a range(Google Sheet功能可以从一个范围内获取日期)

转载 作者:bug小助手 更新时间:2023-10-25 13:28:00 24 4
gpt4 key购买 nike



Is there a function in google sheets to get a list of dates from a range, given that the range has text and date types.
i tried using the filter formula and is date as following:

在google sheets中是否有一个函数可以从一个范围中获取一个日期列表,假设这个范围有文本和日期类型。我尝试使用过滤器公式,日期如下:


=FILTER(H45:J45, ISDATE())

=过滤器(H45:J45,ISDATE())


This didnt work, but all my search shows that these 2 formulas are my best bet to make this work.

这并不管用,但我所有的搜索显示,这两个公式是我最好的选择。


Any help is appreciated

如有任何帮助,我们不胜感激


更多回答
优秀答案推荐


You could try:

您可以尝试:


=filter(H45:J45, isdate_strict(H45:J45))


Try this

尝尝这个


=FILTER(H45:J45, ISTEXT(H45:J45)<>true)

Explanation


This formula =FILTER(H45:J45, ISTEXT(H45:J45)<>true) works because it filters a range (H45:J45) to include only cells that contain text values. The ISTEXT function checks if a cell contains text, and the <> true part ensures that only cells where the result is not equal to "true" (i.e., cells that do not contain text) are included in the filtered result.

此公式=FILTER(H45:J45,ISTEXT(H45:J45)<>TRUE)之所以有效,是因为它筛选区域(H45:J45)以仅包括包含文本值的单元格。ISTEXT函数检查单元格是否包含文本,而<>TRUE部分确保过滤后的结果中只包括结果不等于“TRUE”的单元格(即不包含文本的单元格)。


The OP formula =FILTER(H45:J45, ISDATE()) doesn't work as intended because the ISDATE function is not designed to check for the presence of dates in a range. Instead, it checks if a given value is a valid date. Since you didn't specify a value within the ISDATE function, it treats the function as if it's checking the entire range (H45:J45) for date values, which isn't its intended use.

OP FORMULA=FILTER(H45:J45,ISDATE())不能按预期工作,因为ISDATE函数不是为检查某个范围中是否存在日期而设计的。相反,它会检查给定值是否为有效日期。因为您没有在ISDATE函数中指定值,所以它将该函数视为正在检查整个范围(H45:J45)中的日期值,这并不是它的预期用途。


更多回答

Thanks for the upv0te @rockinfreakshow

感谢你的upv0te@rockinfreakshow

Hey @Osm, Uhh sorry that wasnt me...

嘿@OSM,呃抱歉那不是我...

so lets assume that in my range there are mixed types of data lets say numbers dates and text, the mentioned formula would include all data types except for text. Is that correct ?

因此,让我们假设在我的范围内有混合类型的数据,比如数字、日期和文本,上述公式将包括除文本之外的所有数据类型。对吗?

yes this is correct

是的,这是正确的

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com