Have you ever needed to find the last 7 working days from the current date in Google Sheets? Well, look no further! In this article, we’ll explore a formula that can help you achieve just that. So, let’s dive in and discover how to identify those crucial dates.
A Handy Formula for Identifying the Last 7 Working Days
If you have a column of sequential dates, the formula we’re about to share will return seven TRUE values, allowing you to easily spot the last 7 working days. But that’s not all! This formula also gives you the flexibility to specify weekend days and other holidays if needed.
In Google Sheets, you can use either the WORKDAY or WORKDAY.INTL function to determine the start of the last 7 working days (business days) from the current day. Here are the formulas you can use:
=WORKDAY(today(),-7)
=WORKDAY.INTL(today(),-7)
As a side note, the default weekends in these formulas are Saturday and Sunday. But don’t worry, we’ll show you how to change that later on.
Mapping Date Range to Find the Last 7 Working Days in a Column
Now, let’s explore a more advanced approach using the recently launched MAP function, also known as the Lambda Helper Function, along with some date functions. This method allows you to find the last 7 working days in a column easily.
First, let’s take a look at some sample data:
In the above example, let’s assume the current day is 28/09/2022. You can use the formula =TODAY() in any cell to retrieve the current date. The cells highlighted in Amber color represent the last 7 business days.
To identify those dates, enter the following formula in cell F2. This formula returns TRUE against those cells in the range provided that F3:F is blank:
=MAP(C2:C, LAMBDA(wd, IFERROR(AND(WORKDAY(wd-1,1)=wd, ISBETWEEN(wd, WORKDAY(TODAY(),-7), TODAY()-1)))))
In short, this MAP formula helps us find the last 7 working days in Google Sheets. And that’s not all! You can also use this formula as the range in SUMIF to get the total quantity in the last seven business days. Here’s the syntax for the SUMIF formula:
=SUMIF(map(C2:C,lambda(wd,iferror(and(workday(wd-1,1)=wd,isbetween(wd,WORKDAY(today(),-7),today()-1))))),TRUE,E2:E)
Now, let’s dive into the explanation of the formula:
Explaining the Sumif Formula (Bolded Part)
First, we need to test whether the date in the first row satisfies two conditions:
- Find out if the date falls between the starting point of the last 7 working days and the current date.
- Ensure that the date in question is a business day.
To achieve this, we use the following formulas:
Condition_1 Formula:
=WORKDAY(C2-1,1)=C2
Condition_2 Formula:
=ISBETWEEN(C2, WORKDAY(TODAY(),-7), TODAY()-1)
To find the last seven working days, we test whether the above two conditions evaluate as TRUE using the AND function:
And_Test:
=AND(WORKDAY(C2-1,1)=C2, ISBETWEEN(C2, WORKDAY(TODAY(),-7), TODAY()-1))
By dragging the fill handle of cell F2 down, you will obtain the desired result. However, it’s worth noting that this step is not necessary thanks to the MAP function.
The MAP function maps each value in the given range (C2:C) to a new value (TRUE or FALSE), providing an array result. This eliminates the need for dragging the formula down and enables you to find the last 7 working days with ease.
To use the MAP function, replace the and_test formula in cell F2 with the corresponding formula above, replacing all cell references C2 with wd, as specified in the lambda function.
Finding the Last 7 Working Days and Excluding Specific Weekends and Holidays
What if you want to exclude specific weekends and holidays when finding the last 7 working days? We’ve got you covered!
To exclude holidays, enter the holiday dates in cells G2:G3. Then, modify the formula as follows:
- Replace
WORKDAY(wd-1,1)withWORKDAY(wd-1,1,G2:G3) - Replace
WORKDAY(TODAY(),-7)withWORKDAY(TODAY(),-7,G2:G3)
To specify weekends, replace WORKDAY with WORKDAY.INTL and specify the weekend number. Don’t forget to reference the date functions to get the correct weekend numbers.
Furthermore, if you prefer, you can replace the MAP function with BYROW, which will also work without any other changes.
And there you have it! With these formulas and techniques, you can effortlessly find the last 7 working days in Google Sheets. Say goodbye to manual calculations and hello to productivity!
Thank you for reading, and if you want to explore more tips, tricks, and resources for Google Sheets, be sure to visit Crawlan.com. Happy sheeting!
