Find the most recently passed Thursday based on Today's date

MF NEOM
MF NEOM ✭✭✭
edited 04/30/23 in Formulas and Functions

I want to count the number of project manager status update sheets that are not updated to the most recently passed Thursday. All the project managers are supposed to update their status update sheets every Thursday. On the following Sunday/Monday, I will check and see which status update sheets are not updated to the most recently passed Thursday.

Ideally I want a summary field that tells me the count of sheets not updated to the most recently passed Thursday. For instance, 12 of the 41 status updates sheets have not been updated.

This formula will need to be dynamic as my check date could differ. For instance, if I check everything on a Monday, the formula still needs to identify the most recent Thursday which was 4 days ago (if I assess things on a Monday).

Best Answer

  • Brian_Richardson
    Brian_Richardson Overachievers
    edited 05/01/23 Answer ✓

    This formula will always give you the date of the previous week's Thursday, regardless of what day it is. This needs to be in a date column.

    =Today() - WEEKDAY(Today()) - 2

    You can then incorporate this into a formula to check your update dates against the "last Thursday date" and see if they are before or after. This formula goes in a Text/Number column. Something like:

    =if([Update Date]@row>=[Thursday Date]@row,"Updated","Not Updated")

    If you don't want separate columns (I like separate columns to break up formulas...but your call!) then you can combine these formulas like below in a Text/Number column:

    =IF([Update Date]@row >= (TODAY() - WEEKDAY(TODAY()) - 2), "Updated", "Not Updated")

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers
    edited 05/01/23 Answer ✓

    This formula will always give you the date of the previous week's Thursday, regardless of what day it is. This needs to be in a date column.

    =Today() - WEEKDAY(Today()) - 2

    You can then incorporate this into a formula to check your update dates against the "last Thursday date" and see if they are before or after. This formula goes in a Text/Number column. Something like:

    =if([Update Date]@row>=[Thursday Date]@row,"Updated","Not Updated")

    If you don't want separate columns (I like separate columns to break up formulas...but your call!) then you can combine these formulas like below in a Text/Number column:

    =IF([Update Date]@row >= (TODAY() - WEEKDAY(TODAY()) - 2), "Updated", "Not Updated")

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!