Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for
Did you mean:
Creator III

How to convert number of days into Month and Year

Hi,

How i can convert number of days coming through 2 date differences into number of months and year.

I want number of months like 15,28,60 that way.

1 Solution

Accepted Solutions
Specialist

May be this:

(year(today())*12+month(today()))-(year(start_date)*12+month(start_date))  as NumofMonths

and

((year(today())*12+month(today()))-(year(start_date)*12+month(start_date)) )/12 as NumofYears

18 Replies
Master II

im not sure if i understood your requirement.

Try to create a straight table with policynumber and startdate as dimension

and then add this expression:

date(num(date(start_date,'DD-MM-YYYY'))+num(Number_of_days))

hope this helps

Creator III
Author

Hi,

I actually want to convert the difference of number of days between  (today - Start_date) into number of Months as well as Year.

like Date difference between 1-Mar-2003 to 27-Feb-2017 is 5112 days. I want out-put to be convert into number of Months and Year.

MVP

This would be based on 365 days in an year? I mean what would 5112 correspond to outside of QlikView?

Master II

dim:

-policynumber

-startdate

exp:

-num(Number_of_days)

-num(subfield(num(Number_of_days)/365,'.',1))      for years

-floor(num(0.&subfield(num(Number_of_days)/365,'.',2))*365)        for days

hope this helps

Specialist

May be this:

(year(today())*12+month(today()))-(year(start_date)*12+month(start_date))  as NumofMonths

and

((year(today())*12+month(today()))-(year(start_date)*12+month(start_date)) )/12 as NumofYears

Creator III
Author

Hi Sunny,

Whether in Qlik we have any formula which will convert the date difference depending upon 365 or 366 days in a Year.

So I will get the accurate out-put rather than dividing by 30 or 31 days.

same way for to know the year rather to divide by 365 / 366 days.

Partner - Champion III

Using your own example dates, what would you like to get for 27-Feb-2017 vs 1-Mar-2003 (in Days, Months, Years)?

And for 7-Jan-2016 vs 7-Dec-2015?

MVP

Hi,

maybe one solution might be:

```tabPolicies:
Div(AgeMonthsTotal,12) as AgeYears,
Mod(AgeMonthsTotal,12) as AgeMonths;
start_date,
Month(Today())-Month(start_date)+(Year(Today())-Year(start_date))*12 as AgeMonthsTotal
FROM [https://community.qlik.com/servlet/JiveServlet/download/1221326-267639/No_of_days.xlsx] (ooxml, embedded labels, table is Sheet1);
```

hope this helps

regards

Marco

Creator III
Author

Hi Aar Kay,

Thanks all for your support..

It is working but only thing is if Months or Year are coming like 12.5 and above then that month or Year should rounding up to 13.

Community Browser