Rounding to nearest Thursday in the past or current day if it's Thursday

Hello! Long time listener, first time caller —

We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the current Thursday, if today is Thursday.

In other words, the date should always display a Thursday date; if today == Thursday, display that date, if today != Thursday, display previous week's Thursday date.

I've been able to get the previous week's Thursday displaying via "=TODAY() - WEEKDAY(TODAY()) - 2" in a field in the sheet summary view, however it does consider if today is Thursday or not. Is there a way to do both in one formula?

Find more posts tagged with

Sheets

Formulas

Solution Center

Dashboards

Accepted answers

Melissa Yamada

Hello @aparadowski

Try this:

=IF(WEEKDAY(TODAY()) = 5, TODAY(), TODAY() - WEEKDAY(TODAY()) - 2)

All comments

Melissa Yamada

Hello @aparadowski

Try this:

=IF(WEEKDAY(TODAY()) = 5, TODAY(), TODAY() - WEEKDAY(TODAY()) - 2)

aparadowski

@Melissa Yamada you're a wizard, that worked!

Is my read on what each part of the formula doing correct? The original formula I used I found on the forum and tweaked until it worked, but I admittedly am not wrapping my head around the how.

If today is a weekday = 5 (which means Thursday), display today's date.

Else display today's date minus ???

Melissa Yamada

@aparadowski yes correct!

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.