Solved

# Assess Date

Posted on 2014-08-11
194 Views
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
Question by:Seamus2626

LVL 27

Expert Comment

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

Author Comment

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

Thanks
0

LVL 27

Accepted Solution

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

Author Closing Comment

Thanks Glenn!
0

LVL 27

Expert Comment

You're welcome.
0

## Featured Post

### Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.