EXCEL:如何找出一个单元格日期是“本周”、“上周”或“前一周”。
EXCEL:如何找出一个单元格日期是“本周”、“上周”或“前一周”。
提问于 2022-03-02 13:02:45
我有一张表，在A栏中有不同的日期(“工作日期”)
列B(“周数”)将这些日期作为周号列出
列C(“周”)应显示该日期是本周，上周，前一周，或更大或在未来。
我试过以下公式，但它只是显示了一切都是“老”
=IF( (WEEKNUM(TODAY()) - WEEKNUM([@[Week Number]])) < 0,
"Future",
IF( (WEEKNUM(TODAY()) - WEEKNUM([@[Week Number]])) = 0,
"This Week",
IF( (WEEKNUM(TODAY()) - WEEKNUM([@[Week Number]])) = 1,
"Last Week",
IF( (WEEKNUM(TODAY()) - WEEKNUM([@[Week Number]])) = 2,
"Previous Week",
"Old"
)
)
)
)我也尝试过WEEKNUM(Now())，但是没有什么区别。
我做错了什么？
提前感谢
DD
回答 3
Stack Overflow用户
回答已采纳
发布于 2022-03-02 13:26:53
您不需要在WEEKNUM函数中包装Week Number列。
=IF(
WEEKNUM(TODAY()) - [@[Week Number]] < 0,
"Future",
IF(
WEEKNUM(TODAY()) - [@[Week Number]] = 0,
"This Week",
IF(
WEEKNUM(TODAY()) - [@[Week Number]] = 1,
"Last Week",
IF(
WEEKNUM(TODAY()) - [@[Week Number]] = 2,
"Previous Week",
"Old"
)
)
)
)如果您使用的是Excel 2019或更高版本，则可以使用IFS函数使其更易于阅读：
=IFS(WEEKNUM(TODAY())-[@[Week Number]]<0,"Future",WEEKNUM(TODAY())-[@[Week Number]]=0,"This Week",WEEKNUM(TODAY())-[@[Week Number]]=1,"Last Week",WEEKNUM(TODAY())-[@[Week Number]]=2,"Previous Week",TRUE(),"Old")如果使用Excel 365或更高版本，则可以使用LET函数为当前周数创建变量，然后检查该变量以分配状态。SWITCH函数的执行方式非常类似于IFS
=LET(weekNumber,WEEKNUM(TODAY())-[@[Week Number]],SWITCH(TRUE(),weekNumber<0,"Future",weekNumber=0,"This Week",weekNumber=1,"Last Week",weekNumber=2,"Previous Week","Old"))Stack Overflow用户
发布于 2022-03-02 13:24:20
在使用标准IF函数时，如果首先列出您想要的选项列表a，会更容易一些，因为您将需要与选项一样多的ifs 减去1。所以你的选择是：
- 上周
- 本周
- 下周
H 112未来周H 213G 214
这是5个选项，所以你需要4个IFS。我的公式是：
=IF(B1=WEEKNUM(TODAY())-1;"Last Week";IF(B1=WEEKNUM(TODAY())+1;"Next week";IF(B1<WEEKNUM(TODAY());"Older week";IF(B1>WEEKNUM(TODAY());"Future week";"This Week"))))Stack Overflow用户
发布于 2022-03-02 13:40:48
如果你有不同年份的约会，那么使用周末可能是很关键的。
如果您使用Excel 365，我建议使用LET-公式：
=LET(
mondayDate, [@Date]-WEEKDAY([@Date],2),
mondayToday,TODAY()-WEEKDAY(TODAY(),2),
diffWeeks,(mondayToday - mondayDate)/7,
diffWeeks2,IF(diffWeeks<0,1,IF(diffWeeks>3,2,diffWeeks +3)),
CHOOSE(diffWeeks2,"Future","old","this week","last week","previous week"))前两个步骤检索原始日期和今天的星期一。
减去并除以7，结果在周的差异。
为了能够使用returns函数，diffWeeks2返回
- 1用于未来日期
- 2用于超过3周的日期
- 用于所有其他日期3，因此可以检索到正确的措辞
选择，然后返回正确的措辞。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/71323169
复制相关文章
相似问题
腾讯云开发者
