Calculate remaining days in a timeline

Hi there,
I’m looking to build a formula which returns the number of days remaining in the Timeline column, compared to today. The formula is aware of the date of today, for example if today’s date is January 1st and the timeline is from 10th to the 15th of January, the formula will still return 6 (not 16). If today’s date is the 11th of January, the formula will return 5. If today’s date is the 16th of January, the formula will return 0. I’ve tried using the AI assistant which has given me a variety of formulas using the {Timeline#start} variable but unfortunately this is not really going anywhere yet. Does anyone have any ideas as how to achieve this?

Hi Gus,

Try:

IF(FORMAT_DATE(TODAY(),"YYYY-MM-DD")<{Timeline#Start},DAYS({Timeline#End},{Timeline#Start}),IF(FORMAT_DATE(TODAY(),"YYYY-MM-DD")>{Timeline#End},0,DAYS({Timeline#End},FORMAT_DATE(TODAY(),"YYYY-MM-DD"))))

If the timeline starts after today’s date, it returns the number of days in the timeline.
If the timeline ended before today’s date, it returns 0.
In other scenarios, it returns the number of days between today and the end of the timeline (you may want to add 1 to this scenario if you want to count today).

Hope it helps.

 

Want to take your formulas to the next level? Try the Advanced Formula Booster, the app that reinvents formulas in monday.

  • Create formulas without using the Formula column (and avoid its limitations)
  • Build formulas involving data from the previous item, the next item, the sub-items, the parent item, even items in the same group or the same board.
  • In one formula, update multiple columns from multiple items.

Check our blog for real use cases.

Wow that was quick! thank you! Sorry for my ignorance, I’ve tried to include the +1 in the formula to get the correct number of timeline days but cannot seem to get the right result. Monday newbie here… Thanks anyhow!

See below: I added 1 to the case when today is within the timeline. Hope this is what you need.

IF(FORMAT_DATE(TODAY(),"YYYY-MM-DD")<{Timeline#Start},DAYS({Timeline#End},{Timeline#Start}),IF(FORMAT_DATE(TODAY(),"YYYY-MM-DD")>{Timeline#End},0,1+DAYS({Timeline#End},FORMAT_DATE(TODAY(),"YYYY-MM-DD"))))

What if we could break free of the Formula column? and write formulas that update any type of columns? What if a formula could update multiple columns at once? This is possible with the Advanced Formula Booster app.

Thank you Gilles Cavin for your help on this one!