[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

Assess Date

Hi,

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

Thanks
0
Seamus2626
Asked:
Seamus2626
  • 3
  • 2
1 Solution
 
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?

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

Thanks
0
 
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

Regards,
-Glenn
EE-DateAging.xlsx
0
 
Seamus2626Author Commented:
Thanks Glenn!
0
 
Glenn RayExcel VBA DeveloperCommented:
You're welcome.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now