Assess Date

Posted on 2014-08-11
Last Modified: 2014-08-12

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

Question by:Seamus2626
    LVL 27

    Expert Comment

    by:Glenn Ray
    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?


    Author Comment

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

    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


    Author Closing Comment

    Thanks Glenn!
    LVL 27

    Expert Comment

    by:Glenn Ray
    You're welcome.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    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.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now