Setting up a Dynamic View based on a 'Week Commencing'

Currently, the HR team manually pull a list of new starts, transfers and leavers for the on a Friday for the following week and send to a mailing list. We are hoping to automate this (as much as possible) in Smartsheet. Each different case type sits in an individual sheet (so leavers in 'Leavers' etc) currently, but I'm experimenting with different ways to show this information (for example, a Dynamic view or a Dashboard with links to Reports).

What I'm getting stuck on is showing only the next week's list of staff; I've been able to add an IF and ISDATE formula to convert all Start/Transfer/Leave Dates the Monday's date as a 'Week Commencing' but not sure how to set this up so each Friday the filter/view automatically shows only the following Monday.

Does anyone have any experience in this?

Find more posts tagged with

Dynamic View

first day of week

Filters

Accepted answers

All comments

Naeem Ejaz

Create a Report

Create a new Report (Grid or Calendar view).
Source Sheet: Select all relevant sheets (Starters, Leavers, Transfers).
Filter:
- Where [Week Commencing] is equal to =TODAY() - WEEKDAY(TODAY(), 2) + 8

This ensures it shows only entries for next week, dynamically updating every Friday.

AdamDoyle

This looks very promising - however, when I add a filter, I only get the option to set the 'equal to' as a date, so I'm not able to add the above formula. (see below)

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.