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 1,484 views

## 1 answer to this question.

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

• 63,700 points

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

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

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

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

+1 vote

## 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

## Date difference: different results in Excel vs. Python

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

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

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

## Excel Formula calculating the starting date of a given calendar week

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

## 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

## 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

## Excel worksheet multi-criteria function (like Index-Match array) to fetch last value of an item, looking up by item name and date

Use: =SUMIFS(C:C,B:B,E12,A:A,MAXIFS(A:A,B:B,E12)) It will return the value at the ...READ MORE