Help! Struggling with building a formula with dates


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?

Hi Jackson,



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 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.

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?

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?

Thank you Gilles for all your help on this!

1 Like