Most recently closed task

NealT

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?

Brian_Richardson

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

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

NealT

Hi @Brian_Richardson

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.

Brian_Richardson

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]), "")

NealT

@Brian_Richardson Amazing!!! Thank you. Very much appreciated!!!