How to show data of previous working day?

For a stacked column chart on a dashboard, I count the number of applicable cases daily. The current formula shows NO DATA on Monday because TODAY(-1) is Sunday.

=COUNTIFS({Customer Escalation Region}, $Region@row, {Customer Escalation Request Reason}, "Close Gap", {Customer Escalation Request Date}, TODAY(-1))

I'm thinking about 'Record date via automation', 'convert the date with Weekday()', 'add If Weekday()=2, use TODAY(-3)'.

If there are good ways to show data of previous working day, please advise me.

Find more posts tagged with

Accepted answers

Nick Korna

Hi @Sachiko Kagami,

The WORKDAY function is what you're after here.

https://help.smartsheet.com/function/workday

In your case, I think the following should work:

=COUNTIFS({Customer Escalation Region}, $Region@row, {Customer Escalation Request Reason}, "Close Gap", {Customer Escalation Request Date}, WORKDAY(TODAY(),-1)

This will do the check for yesterday, skipping weekends. Public holidays aren't automatically in there, but you can add these as per the link.

Hope this helps - if there are any issues then just post! 😊

All comments

Nick Korna

Hi @Sachiko Kagami,

The WORKDAY function is what you're after here.

https://help.smartsheet.com/function/workday

In your case, I think the following should work:

=COUNTIFS({Customer Escalation Region}, $Region@row, {Customer Escalation Request Reason}, "Close Gap", {Customer Escalation Request Date}, WORKDAY(TODAY(),-1)

This will do the check for yesterday, skipping weekends. Public holidays aren't automatically in there, but you can add these as per the link.

Hope this helps - if there are any issues then just post! 😊

Sachiko Kagami

Hi @Nick Korna

Thank you very much for your advice and quick response.

You solved my problem!

Nick Korna

No problem at all, glad to help!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.