I set up a data sheet for calculating dates for possible re-negotiating, based on different terms. See attached file.
I can not solve the today +1,5 month taking into account that number of days are not the same in every month. The easy solution would be just to use 15 days, but it wouldn't be correct and could cause missed deadlines - primarily around February
Also, is there some way to calculate the next "quarter start date" directly in the formula, instead of my current xlookup, it would be appreciated as well.
BR
Susanne
You could use the formula below to return the 1st of the month after the current quarter end:
=DATE(YEAR(D1),((INT((MONTH(D1)-1)/3)+1)*3)+1,1)
Your 1.5 months problem is indeed ambiguous. Half of one month equals 14, 14.5, 15 or 15.5 days. Use 14 days in all calculations. You'll never miss a deadline, though you may be at most 1 day or so early. That formula would be:
=EDATE(D1,1)+14