CODEWITHSUNDEEP
exceldate
user3045867
user3045867

Reputation: 33

Excel formula to check date within this week and last week

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.

Upvotes: 2

Views: 43853

Answers (2)

Guilhem Hoffmann
Guilhem Hoffmann

Reputation: 962

Just check if the difference between the week numbers.

Diff Output
= 0 This week
= 1 Last week
>= 2 Old

Excel formula :

=if( (WEEKNUM(now()) - WEEKNUM(A1)) < 0,
     "Future",
     if( (WEEKNUM(now()) - WEEKNUM(A1)) = 0, 
          "This Week", 
          if( (WEEKNUM(now()) - WEEKNUM(A1)) = 1,
               "Last Week",
               "Old"
            )
       )
   )

Upvotes: 0

barry houdini
barry houdini

Reputation: 46331

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 <=.

Upvotes: 5

Related Questions