Most recently closed task
Is there a way to display just the most recent 5 closed tasks from a sheet on a dashboard?
I have closure dates for when a task has been closed but was wondering if its possible to create a ticker that shows the most recent tasks closed?
Best Answer
-
Ah, yes that conversion from Date to a Number relies on the Completed Date column having a date in it. If it's blank then the formula returns Invalid Data Type into the Date Value column. Because RANKEQ is looking at the whole column, that same error passes through to the Rank column also.
You can fix that by wrapping the two formulas with IFERROR to return blanks. RANKEQ will ignore the blanks.
=IFERROR(YEAR([Completed Date]@row) + MONTH([Completed Date]@row) + DAY([Completed Date]@row), "")
=IFERROR(RANKEQ([Date Value]@row, [Date Value]:[Date Value]), "")
Answers
-
You can use RANKEQ for this. However, the function won't work with dates, so you need to convert the date first into a number and then rank it. You can combine these formulas, but I like to break it out for ease of use:
Date Value column:
=YEAR([Completed Date]@row) + MONTH([Completed Date]@row) + DAY([Completed Date]@row)
Rank column:
=RANKEQ([Date Value]@row, [Date Value]:[Date Value])
Then setup a report that looks at your sheet and filters for Rank < = 5
-
Nice one thats pretty much exaclty what i was looking for.
One question to ask though is, will the rankeq function break if there are tasks are still open and have no completed date as currently in those coloumns its reporting as #invalid data type.
-
Ah, yes that conversion from Date to a Number relies on the Completed Date column having a date in it. If it's blank then the formula returns Invalid Data Type into the Date Value column. Because RANKEQ is looking at the whole column, that same error passes through to the Rank column also.
You can fix that by wrapping the two formulas with IFERROR to return blanks. RANKEQ will ignore the blanks.
=IFERROR(YEAR([Completed Date]@row) + MONTH([Completed Date]@row) + DAY([Completed Date]@row), "")
=IFERROR(RANKEQ([Date Value]@row, [Date Value]:[Date Value]), "")
-
@Brian_Richardson Amazing!!! Thank you. Very much appreciated!!!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives