根据生日日期进行排序，而无需考虑日期的年份

发布于 2025-01-19 11:41:51 字数 460 浏览 0 评论 0原文

我试图根据生日日期对工作表进行排序，最近的生日应该放在第一行，最后一个放在最后，依此类推，要做到这一点，它应该只考虑日期和月份，而不考虑年份排序，所有这些都是通过使用查询功能来实现的，因为数据是从另一张表中查询的。

我尝试过使用以下内容

=SORT(QUERY('Sheet1'!A2:Q, "SELECT * "),6,FALSE)

，但它不起作用，我还尝试通过谷歌表格自定义日期和时间格式化数据以仅显示日期和月份，并且仍然只隐藏年份，但排序仍然考虑年份，如果我应用过滤器进行排序是行不通的，因为它是一个查询函数。有什么建议吗？

工作表链接

原文

I am trying to sort sheet according to birthday date, the closest birthday should go in first row, and the last go to the end and so on, to do this it should only consider the day and month without taking into account the year for the sorting, all this by using the query function since the data is being queried from another sheet.

I have tried using the following

=SORT(QUERY('Sheet1'!A2:Q, "SELECT * "),6,FALSE)

but it is not working, i have also tried formatting the data to show only day and month via google sheets custom date and time, and still only hides the year but for sorting still considers the year, and if I aply a filter for sorting it will not work since it is a query function. Any suggestion?

Link to sheet

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

他不在意 2025-01-26 11:41:51

尝试：

=QUERY(SORT(Sheet1!A2:Q27, TEXT(Sheet1!F2:F27, "mmdd")*1, 1), 
 "where Col6 is not null", 0)

更新：

=ARRAYFORMULA(ARRAY_CONSTRAIN({
 QUERY(SORT({Sheet1!A2:Q27, TO_DATE(TEXT(Sheet1!F2:F27, "2222-mm-dd")*1)}, TEXT(Sheet1!F2:F27, "mmdd")*1, 1), 
 "where Col6 is not null and Col18 >= date '"&TEXT(DATE(2222, MONTH(TODAY()), DAY(TODAY())), "e-m-d")&"'", 0);
 QUERY(SORT({Sheet1!A2:Q27, TO_DATE(TEXT(Sheet1!F2:F27, "2222-mm-dd")*1)}, TEXT(Sheet1!F2:F27, "mmdd")*1, 1), 
 "where Col6 is not null and Col18 < date '"&TEXT(DATE(2222, MONTH(TODAY()), DAY(TODAY())), "e-m-d")&"'", 0)}, 9^9, 17))

try:

=QUERY(SORT(Sheet1!A2:Q27, TEXT(Sheet1!F2:F27, "mmdd")*1, 1), 
 "where Col6 is not null", 0)

update:

=ARRAYFORMULA(ARRAY_CONSTRAIN({
 QUERY(SORT({Sheet1!A2:Q27, TO_DATE(TEXT(Sheet1!F2:F27, "2222-mm-dd")*1)}, TEXT(Sheet1!F2:F27, "mmdd")*1, 1), 
 "where Col6 is not null and Col18 >= date '"&TEXT(DATE(2222, MONTH(TODAY()), DAY(TODAY())), "e-m-d")&"'", 0);
 QUERY(SORT({Sheet1!A2:Q27, TO_DATE(TEXT(Sheet1!F2:F27, "2222-mm-dd")*1)}, TEXT(Sheet1!F2:F27, "mmdd")*1, 1), 
 "where Col6 is not null and Col18 < date '"&TEXT(DATE(2222, MONTH(TODAY()), DAY(TODAY())), "e-m-d")&"'", 0)}, 9^9, 17))

回复收藏 0 原文

~没有更多了~