Formula to check if one date is within a range of other dates?

[kellyfirth]

Sorry if I don't explain this very well, I'll do my best-

I need a formula in B1, which will check the date in A1 and tell me if it is within this week's dates. It will say 'This Week' if this is true. (eg. if A1 says: '30/12/2014' (or any other date from the current week) then it will say 'This Week')

However if the date in A1 is NOT within this week's dates, I need it to check whether it is within last week's dates. If this is true, it will say 'Last Week.' (eg. if A1 says: '25/12/2014' (or any other date from the previous week) then it will say 'Last Week')

If before last week's dates then it will say, 'Over 2 weeks ago.'

I understand this is a little difficult and I'm struggling to wrap my head around it myself. I think I'm able to write this by manually putting in the dates myself, but ideally, I'd like these to just populate everytime the date changes so that I don't have to change them each week if possible.

Let me know if you need any other information and apologies for my lack of clarity!

 

[Colin Legg]

Hi,

Assuming your weeks start on a Sunday, this formula should do it:

=LOOKUP(TODAY()-WEEKDAY(TODAY(),2)-A1,{-6,1,8},{"This Week","Last Week","Over 2 weeks ago"})


It will return #N/A if the date in A1 is in the future ahead of the current week.

 

[kellyfirth]

This was perfect, thank you so much! :D

 

[kellyfirth]

Sorry if it's a bit cheeky to ask, is it possible to make this work for months too?
So it could say: This Month, Last Month, Over 2 months ago?

 

[Colin Legg]

Try:

=LOOKUP(DATEDIF(A1,EOMONTH(TODAY(),0),"m"),{0,1,2},{"Current month","Last month","2 Months ago or more"})

It will return #NUM! if the date in A1 is ahead of the current month.

 

[kellyfirth]

You're amazing <3 Thanks again!