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

aparadowski

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?

Melissa Yamada

Hello @aparadowski

Try this:

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

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!