CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
ajw136
ajw136

Reputation: 39

In Google Sheets, how can you return the next Friday from today, excluding today if today is Friday?

I need to return the next Friday after today in a cell in Google Sheets (format: "dddd d mmm yyyy")

I have used the following formula and this works when today is Monday-Friday but on a Saturday it returns the second next Friday after today instead.

=TEXT(TODAY() + MOD(6 - WEEKDAY(TODAY()), 7) + IF(weekDAY(TODAY()) >= 6, 7, 0), "dddd d mmm yyyy")

Can anyone help me solve this please (and let me know where the error is so I know for next time!)?

Upvotes: 0

Views: 182

Answers (1)

Martín
Martín

Reputation: 10217

The issue is in the >= sign. Saturday is number 7, so you're adding an extra week after today. I'd change it to an equal

=TODAY() + MOD(6 - WEEKDAY(TODAY()), 7) + IF(weekDAY(TODAY()) >= 6, 7, 0)
=TODAY() + 6 + 7

Instead:

=TODAY() + MOD(6 - WEEKDAY(TODAY()), 7) + IF(weekDAY(TODAY()) = 6, 7, 0)
=TODAY() + 6 + 0

Upvotes: 0

Related Questions