Most recently closed task

Options

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?

Tags:

Best Answer

  • Brian_Richardson
    Brian_Richardson Overachievers
    Answer ✓
    Options

    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 | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers
    Options

    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

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • NealT
    Options

    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
    Brian_Richardson Overachievers
    Answer ✓
    Options

    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 | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • NealT
    Options

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