Excel formula to check date within this week and last week

0 votes

Does anyone have a formula for Excel that will make the cells read "this week" if the dates are from this week (today's date), and "last week" if the dates are from the previous week?

Ps: If the date range is neither this week nor last week, the formula should also read "old."

The dates in Column A, for instance, are as follows. Column B should indicate if the date falls within this week or the last week of the current 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
Sep 26 in Others by Kithuzzz
• 20,660 points
68 views

1 answer to this question.

0 votes

Assuming the 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"))

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

answered Sep 29 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
210 views
0 votes
1 answer

Want Tick mark and X-mark using current date and current time using Excel formula

I likewise use a checkmark to indicate ...READ MORE

answered Nov 10 in Others by narikkadan
• 37,660 points
45 views
0 votes
1 answer
–1 vote
0 answers
0 votes
1 answer

Date difference: different results in Excel vs. Python

To calculate the date difference, we can ...READ MORE

answered Feb 23 in Database by gaurav
• 22,040 points
370 views
0 votes
4 answers

How can we compare dates in java?

public static String daysBetween(String day1, String day2) ...READ MORE

answered Sep 5, 2018 in Java by Sushmita
• 6,900 points
442 views
0 votes
1 answer

How can one day be added to a date?

One possible solution could be using calendar ...READ MORE

answered Jun 8, 2018 in Java by Daisy
• 8,120 points
237 views
0 votes
1 answer

How can I calculate number of days between two dates?

You may refer this. This might work ...READ MORE

answered Jul 19, 2018 in Java by Akrati
• 3,190 points
793 views
0 votes
1 answer

IF formula to compare a date with current date and return result

You can enter the following formula in ...READ MORE

answered Sep 27 in Others by narikkadan
• 37,660 points
62 views
0 votes
1 answer

Excel Formula to convert from cm to inches and feet & round off to two digits

Use the TEXT function to format, and ...READ MORE

answered Oct 15 in Others by narikkadan
• 37,660 points
49 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP