Never overlook a prolongation date

The Story

During many years serving customers with managing their domain and/or CMS licenses, it happened from time to time that paying fees due were overlooked. A reason could be that the information about due payments from the providers were not well understood to customers or payment methods on file were expired.

Restoring interrupted services is extremely painful.

To avoid this, I created a SharePoint list that contains the customer license with the contract date and the contract period (1, 2 or 3 years).

The objective to dynamically (daily) calculate the next prolongation/cancellation date sounds easy. However, it took me days to achieve it. Since it is quite a common use-case, I share the solution here.

Basic mathematics

Status	Data	Example
Given	DateOfContract c	18.04.2019
	ContractPeriod p	2
	Today t	13.04.2025
Calculated	Full years since DateOfContract y	5
	Full (contract) cycles n	4
	YearsToAdd (to c) a	6
	NextDueDate d	18.04.2025

Calculate the number of full years y between today and the contract date: y = @TODAY – DateOfContract = (t – c) Excel:=GANZZAHL(DATEDIF(A2;HEUTE();"Y")) where A2 holds the DateOfContractPowerAutomate expression:div(sub(ticks(utcNow()), ticks(variables('varDateOfContract'))), 315360000000000) 315360000000000 = 365 24 60 60 10000000 = Ticks per year (1 Tick = 100 Nanoseconds)
Calculate the completed contract cycles n from the start of the contract: n = y / ContractPeriod = (y / c) = (t -c) Excel: =GANZZAHL(C2/B2) PowerAutomate expression: div(variables('varNumberOfYears'), variables('varContractPeriod'))
Calculate the NextDueDate d for prolongation or cancelation as follows: a= YearsToAdd = (n +1) ContractPeriodExcel: =B2(1+D2) PowerAutomate expression: int(mul(add(variables('var-intCycles'), 1), variables('varContractPeriod'))) d = DateOfContract + YearsToAdd Excel: =EDATUM(A2;12*E2) PowerAutomate expression addToTime(variables('varDateOfContract'), variables('varYearsToAdd'), 'Year')

Implementation in PowerAutomate

Variables used

Name	Type	Purpose/Remarks
varStartTime	String	Start time of the flow: utcNow()
varDebugArray	Array	Holds various data during loops for debug output
varCountWithDate	Integer	Counts the records during loop
varCancelationDate	String	Holds the next due date
varNumberOfYears	Float
varDateOfContract	String
varContractPeriod	Float
varCycles	Float
var-intCycles	Integer	Holds the integer of varCycles
varYearsToAdd	Integer
varEndTime	String	Initialized with utcNow() after loop
varRunTime	String	Initialized with string(div(sub(ticks(variables('varEndTime')), ticks(variables('varStartTime'))), 10000000))

Get assets

Get asset items with DateOfContract not empty sorted ascending