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

**** Hidden Message *****

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

应该是这个了
**** Hidden Message *****
页: [1] 2 3
查看完整版本: 返回当月最后一个星期二的日期，最短代码为最佳[开贴]