"Filter" by Hour and Minute in addition to day(s)

Hello Smartsheet Community!

I have a question. We are setting up a dashboard to display who is on call for specific regions. For some of the regions their on call is easy as it is always Midnight to Midnight but for other they weirder shift times (and people sometimes work "half shifts").

I have my reports for each region set up to display only entries where the start date or end date are today but the division it's for wants them to only show who is actively on call (it's for an emergency service and they want to make sure they there's no confusion even though I have columns for "On-Call Until/After).

So, is there a way to set up a filter that can dynamically reference the current time and check it against those columns? If not a filter then is there a formula/function where I could set it to say "if (date)time is after start and before end, display true"? Or a way to stack formulas within the TODAY() to do so?

Thank you!

Find more posts tagged with

Formulas

Filters

Accepted answers

All comments

L_123

There is no way currently to reference the current time. The easiest way i've found to accomplish something similar to what you are describing is use a login form with a created column, then you can reference the last login date time (automatically collects date hours minutes seconds microseconds etc). You can use some error handling based off the most recent "login" to see if they have failed to login/it's past the shift of the most recent login by using some text parsing tricks on the login sheet.

The most powerful way i've managed to "force" this login is by giving the user the link to the login form, then directing the login form to a published link of a dashboard they need to use to do their job. Then I don't give them any other way to get to said dashboard. You assume they may login many times over the shift if they accidentally close it or have some sort of issue, and if you go in knowing that you can handle it fairly easily.

My solution handled a 3 shift model, 6-2, 2-10, 10-6, and a 2-2-3 model, 12-12, with no problem.

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.