Excel Formula for Every Friday Date in a Date Range List

Formulas & functions · Updated November 18, 2026

With a start date in A2 and an end date in B2, this Microsoft 365 formula returns every Friday in that inclusive date range as a spilling list: =LET(first,A2+MOD(5-WEEKDAY(A2,2),7),IF(first>B2,"",first+7*SEQUENCE(INT((B2-first)/7)+1))). It starts with the first Friday on or after A2, then adds seven days for each following Friday.

What the formula returns

Enter actual Excel dates in A2 and B2, then put the formula in an empty cell. Excel fills the cells below it with the Fridays from the start date through the end date. Both endpoints are included when they fall on a Friday. For example, if A2 is January 1, 2025, and B2 is January 31, 2025, the result is January 3, 10, 17, 24, and 31. January 1 is a Wednesday, so the first Friday is two days later.

The formula uses LET to give the first Friday a short name, first, and then uses that date to calculate how many weekly dates fit in the range. SEQUENCE creates that number of rows, and multiplying its row numbers by seven advances each date by a week. If the range contains no Friday, the formula returns a blank rather than trying to create a zero-row sequence.

This version is intended for Excel for Microsoft 365. SEQUENCE and LET are available in Microsoft 365 and Excel 2021 or later; earlier perpetual versions may not recognize them. The dynamic result also needs clear cells below the formula so Excel can spill the list.

How the Friday calculation works

The key expression is the calculation of the first Friday. With a start date in A2, this formula finds the first Friday on or after that date:

With the start date in A2, enter:

=A2+MOD(5-WEEKDAY(A2,2),7)

WEEKDAY with its second argument set to 2 numbers weekdays from Monday as 1 through Sunday as 7. Friday is therefore 5. Subtracting the weekday number from 5 gives the day difference when the result is positive; MOD(…,7) wraps the difference into the range zero through six. If A2 is already a Friday, the result is zero and the formula returns A2 itself. If A2 is Saturday, it returns the following Friday, not the day before.

For a Wednesday start date, WEEKDAY returns 3, so the adjustment is MOD(5-3,7), or 2 days. For a Saturday, the calculation is MOD(5-6,7), or 6 days. That wraparound is what makes the same formula work for every starting weekday.

Set up the dates and display the results

  1. Enter the earliest date to include in A2 and the latest date to include in B2. For instance, enter January 1, 2025, in A2 and January 31, 2025, in B2.
  2. Choose a blank cell with room beneath it, such as D2, and enter the full formula. Press Enter; in current Excel versions, the results spill into the cells below.
  3. If the output appears as a number rather than a calendar date, select the results and use Home > Number Format > Short Date. Excel stores dates as serial numbers, and number formatting controls how those values look.
  4. Change either endpoint to recalculate the list. Do not type into the cells occupied by the spilled output; doing so can block the result.

In Microsoft 365 for Windows, the steps and formula are the same in Excel for Mac and Excel for the web, although ribbon layout can vary. If Excel rejects commas between function arguments, your regional list-separator setting may require semicolons instead. That is a regional formula-entry difference, not a change to the date logic.

Make a Friday list one row at a time

A simple fill-down method is useful when you want to inspect or edit each row individually, or when your Excel version does not support dynamic arrays. Put the start date in C2 and the end date in D2. In F2, calculate the first Friday with this formula:

With the start date in C2, enter the first-Friday formula in F2:

=C2+MOD(5-WEEKDAY(C2,2),7)

In F3, use the previous result to get the next Friday:

With the first Friday in F2, enter this in F3 and copy it down:

=F2+7

Continue filling down until you reach a date later than the end date in D2. This basic approach is transparent but needs a check at the bottom of the list. To have a copied formula show blanks once it passes the endpoint, use the following in F2 and fill it down. It assumes the start date is in C2 and the end date is in D2:

=IF($C$2+MOD(5-WEEKDAY($C$2,2),7)+7*(ROWS(F$2:F2)-1)<=$D$2,$C$2+MOD(5-WEEKDAY($C$2,2),7)+7*(ROWS(F$2:F2)-1),"")

ROWS(F$2:F2) is 1 in the first output row, 2 in the next, and so on as you copy down. Subtracting 1 makes the first row use zero weekly increments; each later row adds another seven days. The dollar signs keep the input cells fixed while the output-row reference grows. Unlike the spilling formula, this method leaves formulas in the rows that display blanks, so you can fill it farther than the likely list length.

Use an older-Excel alternative

Excel versions without SEQUENCE or LET can still build the list with the fill-down formula above. It uses IF, MOD, WEEKDAY, and ROWS rather than a dynamic-array function. Those traditional functions work in much older Excel versions, so this is a practical option when a workbook must be shared with users on a range of releases.

There is no need to calculate the number of Fridays in advance. Copy the formula down far enough to cover the longest date range you expect, and its blank result will make rows beyond the end date appear empty. If you later extend the end date, the already-filled rows can display the additional Fridays. If there are more dates than rows filled, copy the formula farther down.

For a very old workbook or a workflow where formulas are not wanted, you can also enter the first Friday and add seven days to each subsequent row manually. The formula method is generally easier to update, while manual entry is more prone to missed dates when the range changes.

Choose the right method for your workbook

  • Use the spilling formula when you have Microsoft 365 or Excel 2021 or later and want a concise list that updates automatically when the endpoints change.
  • Use the fill-down formula when you need broad compatibility with older Excel, want blank rows after the end date, or need each output row to be a conventional cell formula.
  • Use the one-step-plus-seven pattern for a short, fixed list where you do not mind stopping the fill yourself at the right endpoint.

These approaches produce calendar Fridays, not necessarily working days for a particular organization. A Friday that is a public holiday still appears because the weekday calculation does not consult a holiday list. Similarly, this is a date-generation task, not a count of business days or a calculation of the last working day of the week.

Common problems and how to fix them

  • The formula shows a number. The result may be a valid Excel date serial displayed with General formatting. Apply a date format such as Short Date.
  • You see #SPILL!. The dynamic formula needs an unobstructed range below its cell. Clear contents from the destination cells, including invisible-looking spaces, or move the formula to a larger empty area.
  • You see #NAME?. Your Excel version may not support LET or SEQUENCE, or a function name may have been mistyped. Use the fill-down alternative in an older version.
  • The result starts on the wrong Friday. Check whether the start date is a real date and whether the formula uses WEEKDAY with 2 as its second argument. With that setting, Friday is 5 and the result is the first Friday on or after the start.
  • The result is unexpectedly blank. Check that A2 is not later than B2 and that both cells contain dates rather than text that only looks like a date. Re-enter the values as dates if necessary.
  • There is one extra date. The formula treats the end date as inclusive. If a Friday equals B2, it belongs in the list. To exclude it, use an end date before that Friday or adjust the requested range.

One frequent misconception is that changing WEEKDAY’s numbering system changes which weekday the formula finds. The number 5 in this formula is correct specifically because WEEKDAY(date,2) numbers Monday as 1. If you change the second argument to another numbering system, you must also change the target number for Friday.

Frequently Asked Questions

How do I list every Friday between two dates in Excel?
Put the start and end dates in A2 and B2, then enter the LET and SEQUENCE formula from the opening paragraph in an empty cell. In Microsoft 365 or Excel 2021 and later, Excel spills the Friday dates into the rows below.

Does the formula include the start date if it is Friday?
Yes. The MOD adjustment is zero when the start date is Friday, so that date is the first result. The end date is also included if it falls on Friday.

How do I get the next Friday after a date?
The formula finds the first Friday on or after the date, so a Friday start returns that same day. If you specifically need the following Friday when the input itself is Friday, add seven days to the returned date.

Can I list Fridays without SEQUENCE?
Yes. Calculate the first Friday, then add seven days in each row, or use the IF and ROWS fill-down formula above to stop with blanks after the end date. This works in older Excel versions that do not support dynamic arrays.

Why does Excel show a number instead of a date?
Excel stores dates as serial values. Select the output cells and apply a date format, such as Home > Number Format > Short Date, to display them as calendar dates.

Keep the date range meaningful

The main decision is whether you need a live, automatically expanding list or a traditional column that can be copied down. For a modern Microsoft 365 workbook, the spilling formula is compact and adjusts as soon as either boundary changes. For shared files that may be opened in older Excel, the fill-down method is more compatible. In either case, confirm that the endpoint rule is inclusive, use real Excel date values, and format the output as dates so the list is easy to read.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.