topic Re: How to calculate number of years, months and days from number of days between 2 dates? in QlikView

How to calculate number of years, months and days from number of days between 2 dates?

NenadV — Wed, 12 Nov 2025 13:54:48 GMT

Hi,

for example, this expression "=today() - date(date#('20200920','YYYYMMDD'))"

returns 1879 as a result and I need to show number of years, months and days between those 2 dates.

Thanks

Re: How to calculate number of years, months and days from number of days between 2 dates?

marcus_sommer — Wed, 12 Nov 2025 15:49:48 GMT

Maybe with something like this: Solved: DATEDIFF function in qlik sense - Qlik Community - 2053996

Re: How to calculate number of years, months and days from number of days between 2 dates?

Andrea_Bertazzo — Wed, 12 Nov 2025 16:01:11 GMT

Hi @NenadV

I would use this expression:

=Date(num(today() - date(date#('20200920','YYYYMMDD'))+1),'YY/MM/DD')

the result is 05/02/23 so 5 years 2 months and 23 days. Today is the 12th of November 2025, by the way.
The idea is to use the Num() function to write the difference between the dates in numeric format.
So the middle expression

=num(today() - date(date#('20200920','YYYYMMDD'))+1

calculates the number of days between the 2 dates, that is 1879 plus 1, that is 1880. I will explain later why I added 1.
You can then use the Date() function to transform this number of days in Years, Months and Days.

Notice that the value may be not fully accurate, because when you calculate Date(1879 +2 ,'YY/MM/DD') you are calculating what year, month and day is after the 1st of January 1900. Considering that not all the months have the same umber of days and that not all years have 365 days, there may be up to 2 days (I think) difference with the real value.
Unfortunately I did not find how to get a more precise value.

Ah, why did I add +1 in the formula? It's because QlikView and Qlik Cloud ( I think also Qlik Sense) start calculating dates from the 30/12/1899, so you need to add 1 day to start your date calculation from the first day of the century.

Re: How to calculate number of years, months and days from number of days between 2 dates?

marcus_sommer — Wed, 12 Nov 2025 17:05:36 GMT

Great approach. But I think the logic needs a small adjustment because the date-difference of 1 will return 00/01/01 respectively 1 month and 1 day. Therefore I believe it should be rather look like:

=Date(num(today() - date(date#('20200920','YYYYMMDD'))-30),'YY/MM/DD')

Re: How to calculate number of years, months and days from number of days between 2 dates?

Luis_Galvan — Thu, 13 Nov 2025 00:44:27 GMT

Give a try using this approach

Floor(MonthsBetween([EndDate], [StartDate]) / 12) & ' Years, ' // YEAR
& Floor(Mod(MonthsBetween([EndDate], [StartDate]), 12)) & ' Months, ' // MONTH
& Floor([EndDate] - AddMonths([StartDate], Floor(MonthsBetween([EndDate], [StartDate])))) & ' Days' // DAYS

Explanation of each element

MonthsBetween([EndDate], [StartDate]): This function calculates the total number of full/partial months between the two dates. This determins the years and remaining months.
Floor(MonthsBetween(...) / 12): This calculates the number of full years in the interval.
Floor(Mod(MonthsBetween(...), 12)): The Mod function finds the remainder after dividing the total months by 12, giving you the number of remaining months (0-11).
AddMonths([StartDate], ...): This function calculates a date that is the number of total months (calculated earlier) after the [StartDate].
[EndDate] - AddMonths(...): Subtracting the calculated date (after adding all the full months) from the [EndDate] gives the number of remaining days. Floor() ensures a whole number of days.
& ' ... ' &: This concatenates the calculated numerical values with the descriptive strings (e.g., ' Years, ').

Example

If [StartDate] is '2022-01-15' and [EndDate] is '2024-04-20', the expression would result in:
2 Years, 3 Months, 5 Days

Re: How to calculate number of years, months and days from number of days between 2 dates?

Vegar — Thu, 13 Nov 2025 05:51:43 GMT

I've never seen this smart approach before @Andrea_Bertazzo. I will give it a try next time I run into a similar request.

Re: How to calculate number of years, months and days from number of days between 2 dates?

marcus_sommer — Thu, 13 Nov 2025 07:12:58 GMT

AFAIK there is no monthsbetween() implemented in Qlik (in opposite to tools like Oracle or SAP) - at least the Qlik help + community has no hint to it.

But with a parametrized variable an appropriate customized function could be created, maybe like:

set MonthsBetween = (year($1)*12+month($1))-(year($2)*12+month($2));

Beside this returns this kind of solution a string - even if long versions with YEAR/MONTH/DAY(s) descriptions were skipped - which will need more resources (for the calculation and the storing) which would be probably not really essentially within the most scenarios but keeping the result numeric enables further other formatted versions as well as applying easy possibilities to group and cluster the date-offsets.

Re: How to calculate number of years, months and days from number of days between 2 dates?

Nagaraju_KCS — Thu, 13 Nov 2025 12:56:15 GMT

It's really Great Approach.

Re: How to calculate number of years, months and days from number of days between 2 dates?

Luis_Galvan — Thu, 13 Nov 2025 16:20:38 GMT

Totally correct. There is no fuction using this calculation in Qlik yet; however, your approach covers what the function usually does; therefore, it can be twist by using a variable like the one you shared.

Thank you for the catch!