[已解决]日期提取当月第几周（有特殊要求）

cyt381828864

老师们，请教一下，怎么通过A列的日期显示D列的第几周？谢谢了

最佳答案

月排行榜 / 总排行榜

baksy

2021-6-4 08:34

=MONTH(INT((A1-2)/7)*7+2)&"月第"&INT(DAY(INT((A1-2)/7)*7+2)/7)+1&"周"

跳转到最佳答案楼层

cutecpu

=LOOKUP(A1,(A1-35+ROW($1:$35))/((WEEKDAY(A1-35+ROW($1:$35))=2)*(DAY(A1-35+ROW($1:$35))<8)),MONTH((A1-35+ROW($1:$35)))&"月第"&1+WEEKNUM(A1,2)-WEEKNUM(A1-35+ROW($1:$35),2)&"周")

祝順心，南無阿彌陀佛！

baksy

=MONTH(INDEX(A:A,LOOKUP(ROW(),ROW($1:$99)*7-6)))&"月第"&WEEKNUM(A1-EOMONTH(LOOKUP(A1,A$1+ROW($1:$99)*7-7),-1))&"周"

一剑惊心

=TEXT(FLOOR(A1-2,7)+2,"m月第")&CEILING(A1-1,7)/7-CEILING(TEXT(FLOOR(A1-2,7)+2,"e-m")-2,7)/7&"周"

baksy

借用三楼公式；
=TEXT(FLOOR(A1-2,7)+2,"m月第")&INT(DAY(FLOOR(A1-2,7)+2)/7)+1&"周"

baksy

=MONTH(INT((A1-2)/7)*7+2)&"月第"&INT(DAY(INT((A1-2)/7)*7+2)/7)+1&"周"

cyt381828864

cutecpu 发表于 2021-6-3 23:45
=LOOKUP(A1,(A1-35+ROW($1:$35))/((WEEKDAY(A1-35+ROW($1:$35))=2)*(DAY(A1-35+ROW($1:$35))

学习了

cyt381828864

baksy 发表于 2021-6-4 08:10
=MONTH(INDEX(A:A,LOOKUP(ROW(),ROW($1:$99)*7-6)))&"月第"&WEEKNUM(A1-EOMONTH(LOOKUP(A1,A$1+ROW($1:$99) ...

谢谢

cyt381828864

一剑惊心 发表于 2021-6-4 08:15
=TEXT(FLOOR(A1-2,7)+2,"m月第")&CEILING(A1-1,7)/7-CEILING(TEXT(FLOOR(A1-2,7)+2,"e-m")-2,7)/7&"周"

谢谢

cyt381828864

baksy 发表于 2021-6-4 08:34
=MONTH(INT((A1-2)/7)*7+2)&"月第"&INT(DAY(INT((A1-2)/7)*7+2)/7)+1&"周"

这个简单好懂一点，感谢各位老师来了