Assess Date


I need some formula to work out whether a data is this week or last week

So if date Serial is within last 7 days "this week"

if date serial is between 7-14 days old, last week

Maybe theres a smarter way?

Basically i have a string of dates and im trying to work out whether its this week or last week

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
It could also depend on if you interpret a week by the days, rather than the dates.  Your algorithm above certainly works in terms of aging:  Current Date minus Date Serial ... if <= 7 then "This Week"; if > 7 and <= 14 then "Last Week").

But, if you're looking at a Sunday - Saturday calendar, and the current Day is a Sunday, then any value <=7 is "Last Week".  And that formula would change if you were on a Monday - Sunday calendar.

Do you need a formula in Excel or VBA?

Seamus2626Author Commented:
I would prefer formula GlenN, but can work fine with VBA....

Glenn RayExcel VBA DeveloperCommented:
Okay, I've created an example workbook showing both methods.

1) Aging:  this simply checks how old the date is and returns "This Week", "Last Week", or "Over Two Weeks"
=IF(TODAY()-A2<=7,"This Week",IF(TODAY()-A2<=14,"Last Week","Over Two Weeks"))
Personally, I would use more-appropriate labels like "Past 7 Days", "7-14 Days", "Over 14 days", but results are the same.

2) Calendar:  this checks to see if the Date Serial is in the current calendar week, previous week, or before.  It's based on a Sunday-Saturday calendar:
=IF(TODAY()-A2<=WEEKDAY(TODAY(),1),"This Week",IF(TODAY()-A2-WEEKDAY(TODAY(),1)>=7,"Before Last Week","Last Week"))

To see the difference in the results, sort by "Date Serial."

The WEEKDAY function can check any day-day range in the second argument; see Microsoft reference:
WEEKDAY function


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Seamus2626Author Commented:
Thanks Glenn!
Glenn RayExcel VBA DeveloperCommented:
You're welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.