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

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).

Find more posts tagged with

Sheet Summary

Accepted answers

Brian_Richardson

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")

All comments

Brian_Richardson

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

=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")

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.