Need help with a formula to calculate how many items on a sheet are past due, how many days

Hello,

I am looking for a formula I can add to a report or a metrics sheet that will show how many items per project are past due and possibly, who they are assigned to, and maybe if possible how many days past due? There are multiple sheets like this that I would be looking to pull data from.

Here is a snippet of a sample plan that shows columns. Thank you in advance!!!!

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

TO get how many days past due, you would need to insert a column with a formula along the lines of

=IF(Status@row <> "Completed", MAX(TODAY() - [End Date]@row))

After that you would create a row report, reference all of your sheets, add the appropriate columns, and set up your filters to show rows that are not completed and the finish date is in the past.

FRP Acct Intg

Hi Paul,

thanks but I cannot add any columns to the above project.

How could I create a report that pulls in from all my projects like above, how many items are past due and who they are assigned to?

Paul Newcome

You would create a row report. When setting up the report builder, you would reference all of the sheets you want pulled in and then add columns and filters as needed.

https://help.smartsheet.com/learning-track/level-1-foundations/reports

FRP Acct Intg

Paul, what if I wanted to create a widget with this information to add to my dashboard? would I use the same row report?

Paul Newcome

It depends on exactly what information you want displayed and how you want to display it. The simplest way is to use a report widget to display the report directly on the dashboard.

FRP Acct Intg

I wanted to show the information in a more consolidated fashion than how the report looks. if they want to drill down deeper that could click on the widget to get to the report.

FRP Acct Intg

Also Paul,

I made a report but it wont let me group by the "Assigned To" Column, do you know why?

Paul Newcome

Grouping is not possible if you have the column set to allow multiple users.

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.