Duration calculation for Timeline column summary

Hello everyone,
Is there a way to get the duration in term of working days for a timeline of a group of items ? By default Monday.com calculate the sum of the duration of each items of the group, that makes no sense… In the example below I’m expecting to have the Duration for the Survey Specs group = 12 days (May 11 - May 26) - Note I’m hiding the WeekEnd instead of the sum of each group item duration…

Hi Samuel,

I do think what monday.com is doing makes sense. It’s summarizing the results of a number column and really anything else would be unexpected behavior. It’s necessary to treat monday.com as a database, even though it presents and like a spreadsheet and if that’s what you’re accustomed to, it can seem idiosyncratic.

In order to get monday.com to do what you’re looking for, you’d need to connect those items somehow, either as subitems of a parent item or as a connection to a summary item on another board. This will allow you to view those summaries as columns in a single item and thereby use them in formulas.

Hopefully that puts you on the right track. Feel free to reach out if you need some more help.

Hi Francis thanks for your detailed answer.
Regarding the suggestion to connect to a summary item on another board, I don’t think it works as - according to my knowledge - it seems not possible to connect “Group” item either as subitems of a parent item or as a connection to a summary item on another board…

To clarify:

  1. Create a separate board (let’s call it the summary board).
  2. Create a Connect Boards column on the summary board and connect it to your main board.
  3. Create an item on your summary board (let’s call it the summary item).
  4. On the Connect Boards column of your summary item, link all of the items you want to sum.
  5. Create a mirror column on your summary board.
  6. Connect the mirror column to your Connect Boards column.
  7. Specify the column you want to mirror (Timeline) and then choose how you want to display it.
  8. Create a formula column, which should be able to use the values from that mirror column.

In other words, you are not connecting the group—you are connecting all of the items you want to summarize.

Thanks again for your help.
I tried your solution but there is an issue with the last step (8.). As there are multiple items linked in the Connect Boards column the Formula based on the associated mirror (Timeline) is not working. I tried to only linked 1 item in the Connect Boards column and in this case the formula worked. So, it seems that the formula based on values from a mirror column associated to multiple (Connect Board) items does not work in Monday.com

Wow… I got there but it was more complicated than I expected. For the record, I really think formulae should be able to read from mirrored Timeline summary column. The functionality is so basic and should be so simple to implement that it’s surprising it doesn’t exist. There’s really no reason for it not to be functional. Nevertheless, I have found a way to do it.

Here’s the workaround:

  1. Create technical formula columns for the start and end date of the timelines on your main board in order to put those dates into YYYYMMDD ISO format:
FORMAT_DATE({Timeline#Start},"YYYYMMDD")
  1. On the summary item, create the following columns (you can hide them all later… it’s also possible to combine them, but let’s keep them separate for this explanation, as it will simplify the logic)

So what’s happening here?
3. Configure the two mirror columns. One each to summarise the ISO formatted start and dates. This will work because the YYYYMMDD format increases with the date. We can then use the summary types of “Min” for start and “Max” for end to get the “Earliest Start” and “Latest End”

  1. We need to remove the commas from the summary to get our “Start Date #” and “End Date #” columns, using this formula:

SUBSTITUTE({Earliest Start},",","")

  1. We can actually use these dates to calculate the workdays (it seems) using the formula WORKDAYS({End Date #}, {Start Date #}), although I’m a little suspicious there could be unexpected behavior. For good measure, it’s best to format them into a more universally accepted style, using the following formula (for the “Formatted” date columns:
CONCATENATE(
    LEFT(
        {Start Date #},
        4
    ),
    "-",
    RIGHT(
        LEFT(
            {Start Date #},
            6
        ),
        2
    ),
    "-",
    RIGHT(
        {Start Date #},
        2
    )
)
  1. You can use and/or reformat these dates in any way you like. They should be correctly parsed by any of the monday.com formula functions, including WORKDAYS.

Hopefully that’s all clear! As with many things in monday.com, getting to the end can be a process, but this is a functional solution. You can hide all the columns you don’t need to see once you’ve implemented this fully and all should work as expected.

Hi! I’m hijacking this thread, hope that’s OK.

I have a similar use case. I’m using a separate board where I have what we call “seasons”, and every season have a column with a start and stop date.
Then we have our project board with projects where I want to select the starting season and ending season, I.e the start and stop date of the project is mirrored.

However, I just can’t figure out how to make it work in a timeline view as it seems like it does not support separate columns for start and stop dates. Can I use a calculating column or something to get the needed timeline column in the timeline view?

Hi Johan,

I think this is a fairly unique issue, so you’d have been fine to make a new post. However, let me try to help.

If I understand correctly, it sounds like you want to do the following:

  1. Associate “Projects” with “Seasons”.
  2. Use that association to define the time range of a Project (i.e. source it from the Season).
  3. Use that range to populate the Timeline widget.

The logical thing to do here would be to use the Timeline column for your Season items. Then link any Season associated to a Project via the same connection column.

You should then be able to mirror an aggregated view of those timelines. That aggregate timeline can be used in a Timeline widget. Here are some related screenshots.

Does that answer your question?

Screenshot 2023-05-29 at 07.29.56
Screenshot 2023-05-29 at 07.30.53

That did the trick, thanks! I just assumed that it wouldn’t manage to sort out the “earliest to latest” :slight_smile:

1 Like

I am having the same issue. when I hover over the Timeline summary (in Samuel Leroy’s case it is May 11-26) the 12 days will show to me, but I have to hover over it. So, that doesn’t really help.

I think the ideal solution beyond all these separate worksheets and formulas is for monday.com developers to add another option in the summary cell of choices. Right now, I can click on the word “sum” of the Duration Summary and choose min, max, count, etc. So, what I suggest is that there is an option to easily pull the 12 days that I can hover over on the Timeline Summary and pull that data into my Duration Summary cell? Especially if these two cells are connected. Can I suggest to make that one of the options in the choices to pick from other than “sum”. Call it “timeline sum”

Hi Francis,

Monday doesn’t allow automations and formulas to be run on mirrored timeline columns (as far as I’m aware) but this solution looks interesting and I wonder if it can be used as a workaround?

Thanks!

So it is possible to use Timelines in formulas. For automations, the start and end dates of Timelines work (in many cases) as Triggers, however there are no automations currently that can be input or modified via actions (Such as “set today” or “push”). That makes them limited.

The issue that my post resolves is using mirrored date/timeline data in formulas.

For example: You may have a high-level (let’s call it “Projects”) board that connects to a low-level (let’s call it “Tasks”) board. By mirroring a date (let’s call it “Due Date”) column from the Tasks board onto the Projects board, where you have a one-to-many relationship, you would end up with a summary of due dates. This is presented as a Timeline in the Projects board and gives you an approximation of the Project timeline, sourced directly from the task due dates—something obviously useful.

The problem is that you cannot use the start and end dates of this column in automations or in formulas. My solution does not allow it to be used in automations (although there are other workarounds for that), however it does allow you to generate start and end dates that can be used in formulas.

Hi Elliott,

Thank for your reply, apologies, I meant mirrored timeline columns (now edited the post).

I have a high level (summary) board with mirrored items of projects on other (native) boards. I have mirrored the timeline column into the summary board. However, I can’t add automations (for example to notify me if the timeline has changed) and I can’t use it in a formula either.

I have attempted to use some of your logic above to separate out the start and end dates on the native boards and mirror those instead, but I am having the same issue.

What I am trying to do is:

  1. Track when timelines change on the summary board
  2. Specifically track if the end date of the timeline changes and by how much, and ideally I would be able to baseline the date somehow, and track against that

Do you know if there is a workaround for that?

Thanks in advance!

This is probably not possible with monday.com automations. The reason being is that you would need to know which task is the start and end in the timeline. There would be too many possible exceptions and variations.

For something like this, I would recommend using either an app (such as general caster or autoboost) or a third party tool such as Make or Zapier to do the same thing. A basic approach could be to make sure that you’re always syncing that start and and date to separate date columns, and then you could make use of monday.com’s automations to trigger various automations based on that date column.

Please feel free to contact me if you’d some help setting this up. It’s beyond the normal remit of what I’d expect a monday.com user to figure out for themselves.