Help! Struggling with building a formula with dates

[jackson.vanslyke]

Hi!

I’m struggling building out a formula for a specific use on my board. I was hoping to build a formula to calculate START DATE + 60 days and then round up to the beginning of the next month. So far I can get the date plus 60 days, but not the rounding part:

RIGHT(LEFT(CONCATENATE(DATE(YEAR({Start Date}),MONTH({Start Date}),DAY({Start Date})+60)),10),6)

I was also trying to do a similar formula to calculate START DATE plus 6 months and then round UP to July 1 or Jan 1 whichever is closer.

Can someone please help me out with this if this is even possible to complete with formulas?

[GCavin (Gilles Cavin - Reinventing Formulas in monday.com)]

Hi Jackson,

Try

FORMAT_DATE(ADD_DAYS(EOMONTH(ADD_DAYS({Start Date},60),0),1),"YYYY-MM-DD")

It adds 60 days to the start date, gets the end of that month with EOMONTH(date, 0), adds 1 day to get the first of the following month, then formats the date as YYYY-MM-DD.

---

Looking for a simpler way to write complex formulas? Check out the Advanced Formula Booster at https://mdboosters.com. It’s a convenient third-party app that simplifies formula writing. With it, you can spread your formulas across several lines, utilize variables, and access dozens of functions not found in the standard formula column.

[jackson.vanslyke]

Hi Gilles,

Thank you for this! This formula worked. Is there any formula to change the date to either July 1 or January 1st depending on if the start date falls after January 1 then change it to July 1st and if falls after July 1st then change it to January 1st of the following year?

[GCavin (Gilles Cavin - Reinventing Formulas in monday.com)]

Hi Jackson,

Just a note: when you get a solution to your problem, it is better to mark the topic as solved and create another post for your next question.

Here is the answer to your second query:

FORMAT_DATE(IF(MONTH({Date})<7,DATE(YEAR({Date}),7,1), DATE(SUM(YEAR({Date}),1),1,1)),"YYYY-MM-DD")

 
 

---

Curious to know how that same formula would look in the Advanced Formula Booster, the 3rd party app that makes it so easy to write formulas?

[jackson.vanslyke]

Thank you Gilles for all your help on this!