Calculate 1st day of next quarter of contract enddate
For an office rental company I’m using a base to administrate rental contracts.
Each rental contract has an enddate on one of the Quarter startdates. I.e. a contract can only end on:
- Quarter 1 (1st of January)
- Quarter 2 (1st of April)
- Quarter 3 (1st of July)
- Quarter 4 (1st of October)
So if the enddate falls in the 2nd quarter, the contract ends on the next quarter (1st of july)
Furthermore a contract has a startdate and cancellation period (3 or 6 months)
I’m looking for a formula that does the following steps:
-
IF Startdate+Cancellation period < Then Today; Then Today; Otherwise: Startdate+Cancellation. Result is an ‘enddate’
-
Determine current Quarter of ‘enddate’. Then Either round up to show 1st day of next quarter OR add one quarter and convert that in the first day of that quarter.
Output needs to be a date formatted in DD-MM-YYYY and can only be
01-01-YEAR, 01-04-YEAR, 01-07-YEAR, 01-10-YEAR
With what combination of operations into a single formula can I accomplish this?
