In this tutorial, we demonstrate how to calculate a timeline using date and duration columns. This approach closely resembles the “Timeline + Duration Column” combo provided by monday.com, doesn’t it? Indeed, but with greater flexibility including the ability to set the timeline from the end date!
If you are not familiar with the Advanced Formula Booster app, designed to reinvent formulas in monday.com, you may want to read The basics of the Advanced Formula Booster. It walks you through the different steps of creating your first formula, then automating it (screenshots + video).
Step 1: add 3 columns to your board
- a date column: name it Reference Date;
- a numeric column: name it Duration;
- a timeline column: name it Timeline.
Step 2: Create 1 formula in the Advanced Formula Booster
Name it Set Timeline and use this syntax:
1: {Timeline}=TIMELINE({Reference Date},{Duration})
We created this function to make it easy to set the values of a timeline. It accepts two parameters: the first is a date, and the second can be either a date or a number.
If a date is provided as the second parameter, it will act as the second date for the timeline. If a number is provided, it will act as the duration in days of the timeline, so the second date will be calculated by adding the specified number of days to the first date.
While the first date typically represents the start date and the second the end date, the Advanced Formula Booster automatically identifies the earlier date as the start date and the latter as the end date.
Step 3: Add 2 automations to your board
First, in the Integrations module of your board in monday.com, add an automation to run the formula when the Reference Date is entered or modified. Choose the “When column changes Run the Booster Formula formula” option.
For more information on how to add an automation, see The basics of the Advanced Formula Booster.
Then do the same with the {Duration} column to ensure the Timeline adjusts automatically whenever the duration is entered or modified.
Now whenever you enter a date or a duration, the timeline is automatically calculated.
What if you want to go backwards?
If you want the {Reference Date} column to be used as the end date instead of as the start date of the timeline, you have 2 options:
- enter a negative value for duration, OR
- modify the formula by adding a minus sign before the {duration} column.
1: {Timeline}=TIMELINE({Reference Date},-{Duration})
Takeaways from this case
- This solution would not be possible without the Advanced Formula Booster which can calculate any column in your monday.com board.
- We designed the Advanced Formula Booster with the end-user in mind and can create any function you may need in the future: the type of functions that make your coding easier, such as the TIMELINE function featured here.
If you encounter any formula causing you headaches, free free to contact us. We will be glad to assist.