Adding more than 12 month to date in QGIS

Question

I'm trying to calculate the end of a contract. I have a date field and a duration field in months, in some cases are years (24, 36, 48 months)

I'm trying "the Excel way" adapted to QGIS.

Excel way:

=DATE(YEAR(A2), MONTH(A2)+A3, DAY(A2))

My QGIS code:

make_date( year("DATE"), month("DATE") + "DURATION", day("DATE") )

This works only when my Date month + Duration are less than 12; otherwise, the expression is invalid.

I can make and if Duration > 12 create a variable year=(Duration//12) and another variable month_r=(Duration%12) but I still have the problem that if my base Date is in December if I add 1 month the function will fail.

Taras · Accepted Answer · 2025-03-04 07:02:03Z

One can create a custom function via the Function Editor, which could employ the PyQt's addMonths(int nmonths) method of the QDateTime class.

Returns a QDateTime object containing a datetime nmonths months later than the datetime of this object (or earlier if nmonths is negative).

from qgis.core import * from qgis.gui import * from PyQt5.QtCore import QDate @qgsfunction(group='Custom', referenced_columns=[]) def add_duration(date_field, duration_field): """ Adds duration represented in month to a date field """ if isinstance(date_field, QDate): return date_field.addMonths(int(duration_field)) #else: #if the field is not of QDate type #work with the datetime and dateutil Python modules

And then utilize it via the Field Calculator:

add_duration("Date", "Duration")

Babel · Accepted Answer · 2025-03-04 11:43:19Z

The expression to use is "DATE" + make_interval( months:="DURATION")

Be aware, however, that this will create "default" months of 30 days each. So adding 12 months from today (2025-03-03) will add 12*30 days, resulting in 2026-02-26.

A more complex formula that returns the same day of the month and considers even cases like November 30 + 3 months resulting in February 28 of next year (last day of this month; see blue row in screenshot below) looks like this:

with_variable( 'newmonth', (month("DATE") + "duration"%12)%12, make_date( year("DATE") + floor ("duration"/12) +floor((month("DATE") + ("duration" %12))/12), if (@newmonth>0, @newmonth, month("DATE")), if ( day(DATE) > 28, case when @newmonth = 2 then 28 when @newmonth in (4,6,9,11) then 30 end, day(DATE) ) ))

Result: Initial DATE (red border), DURATION in months (2nd column), as well as duration converted in years and months (columns 3 and 4, for information purpose only) and the result of the expression in the last column (yellow):

Stack Exchange Network

Adding more than 12 month to date in QGIS

2 Answers 2

Hot Network Questions

Adding more than 12 month to date in QGIS

2 Answers 2

Related

Hot Network Questions