Formula to calculate the next Friday after today

I have the formula below in Excel that calculates the next friday from today's date; so that any time the sheet is opened before a friday it will automatically insert the date for the coming friday, and when that date has passed and the sheet is re opened it will automatically insert the next fridays date in the week ending box.

=TODAY()-WEEKDAY(TODAY(),3)+IF(WEEKDAY(TODAY(),3)>4,11,4)

Any suggestions how I can replicate this in Smartsheet?

Thanks,
Marc

Find more posts tagged with

Sheets

Formulas

Accepted answers

Etienne Desbiens

Here :
=TODAY() + (6 - WEEKDAY(TODAY()))

Have a nice day,

Étienne Desbiens

All comments

Etienne Desbiens

Here :
=TODAY() + (6 - WEEKDAY(TODAY()))

Have a nice day,

Étienne Desbiens

Marc Langeskov

Thank you Étienne.

J. Craig Williams

Marc,

What is supposed to be displayed when opened on Friday or Saturday? Last week or next week's Friday?

The WEEKDAY function returns a 1 for Sunday, regardless of which day is the start of your week (which is configurable in the Project Settings)

I consider that a good thing for this case but has caused problems when dealing with non-Sunday starting customers for other situations.

I believe this solution will return July 8th on the 8th and 9th of this week.

Craig

Marc Langeskov

Hi Craig,

Thanks for your comment; I'm OK with today (Friday) showing today's date (in this case 8th). TAs the sheet is unlikley to be used at a weekend I think it will be OK. However, I plan to monitor it just make sure all works OK for me. The sheet I have a basic agenda for a weekly action call that takes place each Firday - in order that I can minimise my effort in maintaining and distributing it I have a weekly schedule to send the agenda out the day before the meeting and rather than update the date each week I wanted a formula to do that for me - it's just one less thing for me to do

So far so good.

J. Craig Williams

No problem.

I like to help.

Etienne's solution will improve a sheet I'm working on for a client.

Craig

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.