Excel formula to check date within this week and last week

Excel Formulas to Check if a Date Falls Within This Week or Last Week

Learn how to use Excel formulas to efficiently determine if a given date is within the current week or the preceding week, enhancing your data analysis and reporting capabilities.

Analyzing dates in Excel is a common task, especially when you need to categorize data based on timeframes like 'this week' or 'last week'. This article provides robust Excel formulas that allow you to accurately identify if a date falls within the current seven-day period or the previous seven-day period, relative to today's date. These techniques are invaluable for dashboards, reports, and conditional formatting.

Understanding Excel's Date System

Before diving into the formulas, it's crucial to understand how Excel handles dates. Excel stores dates as serial numbers, where January 1, 1900, is serial number 1. Each subsequent day increments this number. For example, January 1, 2023, is 44927. This numerical representation allows for mathematical operations on dates, which is fundamental to our formulas.

Formula for 'This Week'

To determine if a date falls within the current week, we need to compare it against the start and end dates of the current week. Excel's WEEKDAY function returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week. We can use this, along with TODAY(), to calculate the start and end of the current week.

=AND(A2>=TODAY()-WEEKDAY(TODAY(),2)+1, A2<=TODAY()-WEEKDAY(TODAY(),2)+7)

Excel formula to check if a date in cell A2 is within the current week (Monday as first day of week).

Let's break down the formula:

• TODAY(): Returns the current date.
• WEEKDAY(TODAY(),2): Returns the day of the week for today's date, where 2 indicates that Monday is the first day of the week (1=Monday, 7=Sunday). So, if today is Tuesday, WEEKDAY returns 2.
• TODAY()-WEEKDAY(TODAY(),2)+1: This calculates the date of the most recent Monday. For example, if today is Tuesday (WEEKDAY=2), TODAY()-2+1 gives us yesterday's date, which is Monday.
• TODAY()-WEEKDAY(TODAY(),2)+7: This calculates the date of the upcoming Sunday. It's simply 6 days after the calculated Monday.
• AND(...): Ensures that the date in A2 is both greater than or equal to the start of the week AND less than or equal to the end of the week.

Formula for 'Last Week'

Similarly, to check for 'last week', we adjust the start and end dates. We subtract 7 days from the current week's start and end dates to get the corresponding dates for the previous week.

=AND(A2>=TODAY()-WEEKDAY(TODAY(),2)+1-7, A2<=TODAY()-WEEKDAY(TODAY(),2)+7-7)

Excel formula to check if a date in cell A2 is within the last week (Monday as first day of week).

The logic here is identical to the 'This Week' formula, with an additional -7 applied to both the start and end date calculations. This effectively shifts the entire 7-day window back by one week.

flowchart TD
    A[Input Date (e.g., A2)]
    B[TODAY()]
    C["WEEKDAY(TODAY(),2)"]
    D["Start of Current Week (Monday)
    TODAY()-C+1"]
    E["End of Current Week (Sunday)
    TODAY()-C+7"]
    F["Start of Last Week (Monday)
    D-7"]
    G["End of Last Week (Sunday)
    E-7"]
    H{"Is A >= D AND A <= E?"}
    I{"Is A >= F AND A <= G?"}
    J[Result: This Week]
    K[Result: Last Week]

    B --> C
    C --> D
    C --> E
    D --> F
    E --> G
    A --> H
    D --> H
    E --> H
    H -- True --> J
    A --> I
    F --> I
    G --> I
    I -- True --> K

Flowchart illustrating the logic for 'This Week' and 'Last Week' date checks.

Combining 'This Week' and 'Last Week' Checks

You can combine these formulas using an IF statement to categorize dates. For instance, you might want to display 'This Week', 'Last Week', or 'Other' based on the date.

=IF(AND(A2>=TODAY()-WEEKDAY(TODAY(),2)+1, A2<=TODAY()-WEEKDAY(TODAY(),2)+7), "This Week", IF(AND(A2>=TODAY()-WEEKDAY(TODAY(),2)+1-7, A2<=TODAY()-WEEKDAY(TODAY(),2)+7-7), "Last Week", "Other"))

Combined Excel formula to categorize a date as 'This Week', 'Last Week', or 'Other'.

This nested IF statement first checks for 'This Week'. If that's false, it then checks for 'Last Week'. If both are false, it defaults to 'Other'.