This post contains the formula to find the last 7 working days from the current date in Google Sheets and its explanation.

If you have a column of sequential dates, the formula will return seven TRUE values. That will help us identify those dates.

In the formula, if you want, you can use the option to specify the weekend days and other holidays, if any.

We can get the start of the last 7 working days (business days) from the current day using the WORKDAY or WORKDAY.INTL function in Google Sheets.

Here are those particular formulas.

=WORKDAY(today(),-7)
=WORKDAY.INTL(today(),-7)

You May Like: Google Sheets Function Guide [Quickly Learn All Popular Functions].

As a side note, the default weekends in these formulas are Saturday and Sunday. I’ll explain how to change that later.

What about the business days fall between the returned day and the current day?

E.g., today is Wed, 28 Sep 2022. Using the above formula, we can get Mon, 19 Sep 2022, the starting point of the last seven working days.

I want to identify the dates 19 Sep 2022 (Mon), 20 Sep 2022 (Tue), 21 Sep 2022 (Wed), 22 Sep 2022 (Thu), 23 Sep 2022 (Fri), 26 Sep 2022 (Mon), and 27 Sep 2022 (Tue).

Below we will use the recently launched MAP function (Lambda Helper Function) for this with the date functions.

Map Date Range to Find the Last 7 Working Days in a Column

Sample Data:

As per the above example (screenshot), the current day is 28/09/2022

It’s the date that Google Sheets will return if I use =today() in any cell.

The cells highlighted in Amber color contain the last 7 business days.

In cell F2, enter the following formula, which will return TRUE against those cells provided F3:F blank.

=map(C2:C,lambda(wd,iferror(and(workday(wd-1,1)=wd,isbetween(wd,WORKDAY(today(),-7),today()-1)))))

In short, the above MAP formula helps us to find the last 7 working days in Google Sheets.

For example, to get the total quantity in the last seven business days, let’s use the above formula as the range in SUMIF.

Syntax: SUMIF(range, criterion, sum_range)

The criterion must be TRUE and sum_range of course E2:E.

Formula:

=sumif(map(C2:C,lambda(wd,iferror(and(workday(wd-1,1)=wd,isbetween(wd,WORKDAY(today(),-7),today()-1))))),TRUE,E2:E)

Formula Explanation (Bolded Part of Sumif)

First, we may write a formula for the first row to test whether the date in the said row satisfies the below two conditions.

1. Find whether the date in the first row falls between the starting point of the last 7 working days and the current date.
2. The date in question is a business day.

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, test whether the above two conditions evaluate TRUE by following the AND-based generic formula =and(condition_1,condition_2).

And_Test:

=and(workday(C2-1,1)=C2,isbetween(C2,workday(today(),-7),today()-1))

If we drag the fill handle of cell F2 down, we will get our desired result.

But that (dragging the fill handle down) is not required since we have the MAP function.

The MAP function returns an array result formed by mapping each value in the given arrays (C2:C) to a new value (TRUE or FALSE ), and here you go!

Syntax: MAP(array1, [array2, …], lambda)

So in cell F2, we can use =map(C2:C,lambda(wd,and_test) and replace and_test with the corresponding formula above.

In the and_test formula, replace all cell references C2 with wd, the name specified in the lambda, and voila!

Finding the Last 7 Working Days and Excluding Specific Weekends and Holidays

We will start with finding the last 7 working days, excluding holidays.

Assume there are two holidays fall last week. To exclude those days while finding the last seven business days, do as follows.

Enter those holiday dates in G2:G3.

Modify workday(wd-1,1) with workday(wd-1,1,G2:G3) and WORKDAY(today(),-7) with WORKDAY(today(),-7,G2:G3).

What about specifying weekends?

Replace WORKDAY with WORKDAY.INTL and additionally specify the weekend number.

WORKDAY.INTL(start_date, num_days, [weekend], [holidays])

Please check the date functions to get the weekend numbers.

Before concluding, one more thing! You may replace MAP in the formula with BYROW. It will also work without any other changes.

That’s all. Thanks for the stay. Enjoy!