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, 2022 in Others by Kithuzzz
• 28,900 points
179 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, 2022 by narikkadan
• 53,520 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, 2022 in Others by Edureka
• 13,630 points
299 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, 2022 in Others by narikkadan
• 53,520 points
125 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, 2022 in Others by narikkadan
• 53,520 points
74 views
+1 vote
1 answer

Excel or Google formula to count occurrences of an 8-digit number within a text string

To match an eight-digit number, you may ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 53,520 points
71 views
0 votes
1 answer

Date difference: different results in Excel vs. Python

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

answered Feb 23, 2022 in Database by gaurav
• 22,960 points
441 views
0 votes
1 answer

Excel Formula calculating the starting date of a given calendar week

The number of days to take into ...READ MORE

answered Dec 10, 2022 in Others by narikkadan
• 53,520 points
79 views
0 votes
1 answer

Convert text date/time to a real date time in excel

For a date conversion: =DATEVALUE(TEXT(A1,"MM/DD/YYYY")) For a time conversion: =TIMEVALUE(TEXT(A1,"HH:MM:SS")) For ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 53,520 points
62 views
0 votes
1 answer

Excel: How to use LEFT function on a Date formatted cell?

Use this: =TEXT(E1,"hh:mm:ss") READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 53,520 points
65 views
0 votes
1 answer

Is there a way to test a formula result in excel and type it only once, all within one cell and not using a user defined function?

Use the Let function: =LET(Value,A1+B2+C4+G3+B4,IF(Value>10,"No",Value)) I hope this helps ...READ MORE

answered Jan 9 in Others by narikkadan
• 53,520 points
41 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, 2022 in Others by narikkadan
• 53,520 points
138 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