Use the latest date from mirror columns in formula

Platform discussions
,
3

Hi Omer,

I think you may find this post useful: Duration calculation for Timeline column summary - #6 by FrancisElliott

You can use Formula columns as a workaround to get the latest date from a Mirror column. as Giannis says, it is almost certainly due to multiple mirrored items (or perhaps multiple mirrored boards, in which case this solution may not work). Please share screenshots of the mirror column’s config in any case.

Solution

Here is the basic structure and the config for the mirror column. I have this set up with Subitems, however mirrors for other connect boards columns will also work.

Screenshot 2023-10-30 at 08.16.27
Screenshot 2023-10-30 at 08.16.27922×602 35.4 KB

(status column not important)
Screenshot 2023-10-30 at 08.16.34
Screenshot 2023-10-30 at 08.16.34972×626 51.4 KB

The formulas

:test_tube: Date #

FORMAT_DATE({Date},"YYYYMMDD")

:test_tube: Latest Date #

SUBSTITUTE({Latest Date ⚙️},",","")

:test_tube: Latest Date Formatted

CONCATENATE(
    LEFT(
        {🧪 Latest Date #},
        4
    ),
    "-",
    RIGHT(
        LEFT(
            {🧪 Latest Date #},
            6
        ),
        2
    ),
    "-",
    RIGHT(
        {🧪 Latest Date #},
        2
    )
)

How It Works

Basically it converts the date to a number, uses the “Max” number (i.e. the latest date) and then converts that back into a usable date format (YYYY-MM-DD).

I definitely hope monday.com allows us to use values from mirrored date and timeline columns in formulas. I think this would be really simple to implement.