返回当月最后一个星期二的日期，最短代码为最佳[开贴]

liuts · 发表于 2011-5-29 15:00

本帖最后由 liuts 于 2011-5-29 21:16 编辑

如题，比如今年5月31日即为需返回的结果。

天务 · 发表于 2011-5-29 15:42

本帖最后由天务于 2011-5-29 16:09 编辑

回复 liuts 的帖子

好不容易才做出来一个答案，太长了，不好意思交。用IF套了6层。

=(IF(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,))=3,DATE(YEAR(TODAY()),MONTH(TODAY())+1,),"")&IF(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,))=4,DATE(YEAR(TODAY()),MONTH(TODAY())+1,)-1,"")&IF(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,))=5,DATE(YEAR(TODAY()),MONTH(TODAY())+1,)-2,"")&IF(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,))=6,DATE(YEAR(TODAY()),MONTH(TODAY())+1,)-3,"")&IF(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,))=7,DATE(YEAR(TODAY()),MONTH(TODAY())+1,)-4,"")&IF(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,))=1,DATE(YEAR(TODAY()),MONTH(TODAY())+1,)-5,"")&IF(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,))=2,DATE(YEAR(TODAY()),MONTH(TODAY())+1,)-6,""))*1

复制代码

tkgg93 · 发表于 2011-5-29 16:59

=DATE(YEAR(A1),MONTH(A1)+1,1)-MOD(DATE(YEAR(A1),MONTH(A1)+1,1),7)+3

tkgg93 · 发表于 2011-5-29 17:04

=DATE(YEAR(NOW()),MONTH(NOW())+1,1)-MOD(DATE(YEAR(NOW()),MONTH(NOW())+1,1),7)+3

chunlin1wang · 发表于 2011-5-29 17:52

=TEXT(EOMONTH(TODAY(),0)-MATCH(3,WEEKDAY(EOMONTH(TODAY(),0)-ROW(1:7)+1),)+1,"m月d日")

复制代码

没好思路，先上，学习。

wuxingai · 发表于 2011-5-29 18:10

=EOMONTH(NOW(),0)-MOD(7+WEEKDAY(EOMONTH(NOW(),0))-3,7)

环溪人家 · 发表于 2011-5-29 18:13

学习来了！！！

蝶·舞 · 发表于 2011-5-29 19:50

本帖最后由蝶·舞于 2011-5-29 20:05 编辑

=1-FIND(WEEKDAY(EOMONTH(NOW(),0)),"3456712")+EOMONTH(NOW(),0)

放浪形骸 · 发表于 2011-5-29 20:43

游客，如果您要查看本帖隐藏内容请回复

放浪形骸 · 发表于 2011-5-29 20:55

应该是这个了

游客，如果您要查看本帖隐藏内容请回复

		自动登录	找回密码
密码			注册

[习题] 返回当月最后一个星期二的日期，最短代码为最佳[开贴]