Excel公式检查本周和上周的日期 [英] Excel formula to check date within this week and last week

问题描述

有没有人知道一个excel公式，其中如果日期在本周（今天的日期），细胞阅读文本本周，如果日期是上个星期的细胞阅读上周。

Ps：如果公式也可以读取旧，如果日期范围不在本周或上周

示例：列A具有以下日期。我想要列b阅读日期是否在今天的这一周或上周。

 列A列B 
 
 01/11/2013  - 旧
 
 02/11/2013  - 旧
 
 03/11/2013  - 旧
 
 04/11/2013  - 旧
 
 05/11/2013  - 旧
 
 06/11/2013  - 旧
 
 07 / 11/2013  - 旧
 
 08/11/2013  - 旧
 
 09/11/2013  - 旧
 
 10/11/2013  - 旧
 
 11/11 / 2013-旧
 
 12/11/2013  - 旧
 
 13/11/2013  - 旧
 
 14/11/2013  - 上周
 
 15/11/2013  - 上周
 
 16/11/2013  - 上周
 
 17/11/2013  - 上周
 
 18/11/2013  - 上周
 
 19/11/2013  - 上周
 
 20 / 11 / 2013-上周
 
 21/11/2013  - 本周
 
 22/11/2013  - 本周
 
 23/11 / 2013  - 本周
 
 24/11/2013  - 本周
 
 25/11/2013  - 本周
 
 26/11/2013 - 本周
 
 27/11/2013  - 本周
 
 28/11/2013  - 本周
  

非常感谢任何可以解决这个问题的人。

解决方案

假设A2中的第一个日期尝试B2中的这个公式被复制以给出这些结果

$ （A2< TODAY（） - 7，上周，本周））

code>

....但根据我在本周8天的评论，是吗？如果不是，到< =

Does anyone know an excel formula in which if the dates are within this week (Todays date) for the cells to read the text "this week" and if the dates are from the last week for the cells to read "last week".

Ps: If the formula could also read "old" if the date range is not with this week or last week

Example: Column A has the following dates. I want column b to read whether or not the date is within this week or last week from today’s date.

Column A    Column B 

01/11/2013 - Old 

02/11/2013 - Old 

03/11/2013 - Old 

04/11/2013 - Old

05/11/2013 - Old

06/11/2013 - Old

07/11/2013 - Old 

08/11/2013 - Old

09/11/2013 - Old 

10/11/2013 - Old

11/11/2013- Old 

12/11/2013 - Old 

13/11/2013 - Old

14/11/2013 - last week

15/11/2013 - last week

16/11/2013 - last week

17/11/2013 - last week

18/11/2013 - last week

19/11/2013 - last week

20/11/2013- last week

21/11/2013 - This Week

22/11/2013 - This Week

23/11/2013 - This Week

24/11/2013 - This Week

25/11/2013 - This Week

26/11/2013 - This Week

27/11/2013 - This Week

28/11/2013 - This Week 

Big thanks to anyone who can solve this please.

解决方案

Assuming first date in A2 try this formula in B2 copied down to give those results

=IF(A2<TODAY()-14,"Old",IF(A2<TODAY()-7,"last week", "This Week"))

....but as per my comment that puts 8 days in current week, is that right? If not then change both < to <=

这篇关于Excel公式检查本周和上周的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！