How to get the date of the next Friday in Excel

See it work

The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.

How it works

In Google Sheets

The same formula works in Google Sheets: =A2+7-MOD(WEEKDAY(A2)-$E$1,7). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

Common mistakes

• WEEKDAY counts from Sunday = 1, so Friday is 6. With WEEKDAY(A2,2) (Monday = 1) Friday would be 5; don't mix the two numberings.
• Decide whether "next Friday" can be the same day. This formula never returns the order date itself; use the on-or-after version when it should.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Related answers

• How to find the 3rd Wednesday of a month in ExcelFind the first matching weekday, then add 7 days per extra week: =LET(first,EOMONTH(A2,-1)+1,first+MOD($E$1-WEEKDAY(first),7)+7*($E$2-1)).
• How to find the first Monday of the month in ExcelTake the first of the month and add the days to the next Monday: =LET(first,EOMONTH(A2,-1)+1,first+MOD(2-WEEKDAY(first),7)).
• How to find the last Friday of the month in ExcelStep back from the month end to the last Friday: =EOMONTH(A2,0)-MOD(WEEKDAY(EOMONTH(A2,0))-6,7).
• How to calculate the fiscal month (period) number from a date in ExcelCount the months since the fiscal year started: =MOD(MONTH(A2)-$E$1,12)+1, with the start month in E1.
• How to calculate the fiscal quarter from a date in ExcelCount the months since the fiscal year started, then divide by 3: ="Q"&INT(MOD(MONTH(A2)-$E$1,12)/3)+1, with the start month in E1.
• How to calculate the week ending date in ExcelAdd the days left until Saturday: =A2+7-WEEKDAY(A2). Every date in a Sunday-to-Saturday week gets the same week-ending date.

Functions used: MOD · WEEKDAY