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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives