22 Replies Latest reply: Mar 19, 2018 10:07 PM by Tony THOMPSON

# How Do I Get the Number of Months Between Two Dates?

Other posts here in the forii have mentioned Interval() to get the number of days or months between two days.  However, I have not found a format code to get months.  Either of these expressions...

=Interval#(Today() - [Set up date], 'YY-MM-DD')

=Interval#(Today() - [Set up date], 'YY-MM')

...give me the number of days since [Set up date], but I need months.  Using 'MM' gives the value "2614" for all dates from 2005 to yesterday - I have no idea where that number comes from.  The doc page for Interval() is of course useless.

If there's a simpler way to get the number of months between two dates, such as is present in any RDMS (e.g., DATEDIFF() in MSSQL and MySQL, MONTHS_BETWEEN() in Oracle), I am all ears.  If someone suggests adding the difference in years times 12 to the difference in months, I fear I may lose any remaining faith in QlikView.

Thank you!

• ###### How Do I Get the Number of Months Between Two Dates?

You should be able to do the same formula in qlikview.

http://support.microsoft.com/kb/214134

• ###### Re: How Do I Get the Number of Months Between Two Dates?

The EDate() and LDate() functions, useful though they are in Excel, do not exist in QlikView.

• ###### How Do I Get the Number of Months Between Two Dates?

You could always create your own custom function in the module:

Function MonthDiff (startdate, enddate)

' return number of months between two dates

If IsDate(startdate) and IsDate(enddate) Then

MonthDiff = DateDiff("m", startdate, enddate)

End If

End Function

then just use it in the script eg

MonthDiff(text(W1DEL1), text(today()))) * 12))

I never did find an easier way myself!

Regards,

Gordon

• ###### Re: How Do I Get the Number of Months Between Two Dates?

I'm specifically looking for an expression in the QlikView document itself (e.g., a dimension in a pivot table), not the script.  There are over a dozen date metrics in the data set, so it would be impractical to calculate all possible date differences in the script.

• ###### How Do I Get the Number of Months Between Two Dates?

Jonathan,

Since "Month" is not a fixed length interval, you have to define a rule.  For practical purposes, this can be a reasonable approximation:

round((interval#(Today()-[Set up date], 'DD')/30.5), 0.1)

• ###### Re: How Do I Get the Number of Months Between Two Dates?

The length of months differs, but the number of months between two dates is defined quite consistently across most or all RDMSs: the number of month boundaries crossed from A to B.  Any day in February to any day in March is one month; any day in March to any other day in March is zero months, etc.  Taking the number of days and dividing it by 30.5 would give inaccurate results.

Using 30.438333 as a divisor would be close over the long term, but would still report 03/01 to 03/31 as being a period of one month, against this business need and (IMHO) against most people's intuitive sense.

• ###### Re: How Do I Get the Number of Months Between Two Dates?

Sorry to shake your faith in QV , but the expression I would use is:

=((year(today(2))*12)+month(today(2))) - (((year([Set up date])*12)+month([Set up date])))

You can ease your pain somewhat by creating a variable function in the script:

SET MonthDiff=((year(today(2))*12)+month(today(2))) - (((year(\$1)*12)+month(\$1)));

and then in the chart:

=\$(MonthDiff([Set up date]))

-Rob

• ###### How Do I Get the Number of Months Between Two Dates?

ue below  code

Interval(Today() - [Set up date], 'mm')

• ###### Re: How Do I Get the Number of Months Between Two Dates?

Tried that.  As reported above, it gave a value which was much too high.  Expanding on the example, if I use this formula:

='From ' & Text(Today())

& ' to '

& Text(Date#([Set up date]))

& ' is '

& Interval#(Today() - Date#([Set up date]), 'MM')

& ' months'

...I get values like this:

 From 2012/02/20 to  2006/03/10 is 2173 months From 2012/02/20 to 2006/11/13 is 1925  months From 2012/02/20 to 2006/11/14 is 1924  months From 2012/02/20 to 2006/04/04 is 2148  months From 2012/02/20 to 2008/02/17 is 1464  months From 2012/02/20 to 2007/12/04 is 1539  months From 2012/02/20 to 2007/09/04 is 1630  months From 2012/02/20 to 2007/12/14 is 1529  months From 2012/02/20 to 2008/01/11 is 1501  months From 2012/02/20 to 2008/02/13 is 1468  months From 2012/02/20 to 2008/03/21 is 1431  months

So it would appear that the 'MM' parameter in Interval#() gives days.  How this could possibly be a good idea, I do not know.

Even more bizzarely, the simpler expression "=Interval#(Today() - Date([Set up date]), 'MM')" returns 2,617 in every row, even when the more elaborate expression get different numbers from the very same Interval() call.

Is this all an experiment by the Great Old Ones to drive us mad?

• ###### How Do I Get the Number of Months Between Two Dates?

Jonathan,

Now you have the rule: "Any day in February to any day in March is one month; any day in March to any other day in March is zero months, etc".  My initial understanding was quite different...

I think it makes sense to assign unique sequence numbers to the months in the master calendar.  It will help to calculate the difference on the front end.

As for the interval# using 'MM" - don't expect it to work.  It accepts DD, hh, mm, and ss, unless there were some recent additions.

Regards,

Michael

• ###### Re: How Do I Get the Number of Months Between Two Dates?

I was hoping to avoid that, since it adds one more megabyte of data (assuming 50% compression) to an average file.  Since monthly calculations don't come up very often, I'm leaning toward Rob's solution of a

Re: the use of "MM": thanks for spelling that out.  The QV10 doc only gives the examples "YY-MM-DD", "hh:mm:ss", "D hh:mm", but is not explicit about how the codes are interpreted.  It seems the answer is "the number of days between the dates."  Except when it's not.

• ###### Re: How Do I Get the Number of Months Between Two Dates?

Yeah, my faith is pretty much curled up a corner sobbing by now.  Your solution is very graceful though, and it raises a productive avenue I'll try to keep in mind in the future (UDFs).  The only thing that concerns me is explaining to the users that this method has to be called with the \$(...) surround.

The parentheses in your function were making my eyes cross, so I simplified it slightly:

`SET MonthDiff=(Year(\$1)*12 + Month(\$1) - (Year(\$2)*12 + Month(\$2)));`

Called as such:

`=\$(MonthDiff(Today(), [Set up date]))`

EDIT: For consistency with similar functions in RDMSs, it would probably be better to express the logic as parameter B - parameter A, or putting it another way, "how many months passed from A to B?".

```SET MonthDiff=(Year(\$2)*12 + Month(\$2) - (Year(\$1)*12 + Month(\$1)));
```

Results:

```=\$MonthDiff('2000-01-01', '2012-02-20') = 145
```

Too bad QlikView doesn't support method overloading, or we could have a single-param version which used Today() for the second parameter implicitly.

• ###### Re: How Do I Get the Number of Months Between Two Dates?

You can provide for an optional second parameter with a default of today like this:

SET MonthDiff=(if(len(\$2)=0

,(year(today(2))*12)+month(today(2))

, (year(\$2)*12)+month(\$2))

) - ((year(\$1)*12)+month(\$1));

I'll leave it to you to improve the parens

-Rob

http://robwunderlich.com

• ###### Re: How Do I Get the Number of Months Between Two Dates?

Hi Rob,

I am still lost in your expression. being New to QV & Scripting but is been like thrown into the deep end.

Data set i have:

 Start Date Completed Date 21-Mar-1997 31-Dec-2030 01-Jun-2010 31-Dec-2030 10-May-2013 31-Dec-2030

1. find the number of months between the dates
2. find the number of Years between the dates
3. Do not load/(drop) data below certain year stamp

SUP500:

District,

[Contract No],

if(Left([Contract No],3)='FPA','FPA','CONTRACT') as TYPE,

[Tender No],

[Supplier Name],

[Contract Description],

[Start Date],

[Completed Date],

SET MonthDiff=((year[Completed Date])*12)+month[Completed Date])) - (((year([Start Date])*12)+month([Start Date]))),

Month([Start Date]) as [Start Month],

Year( [Start Date]) as [Start Year],

• ###### Re: How Do I Get the Number of Months Between Two Dates?

Hi Jonathan,

I always work with the30.4 as divider. This is the average nr of days per month in a year. I did not find any miscalculation so far.

t1:

*,

round((EndDate - StartDate)/30.4) as months

;

makedate(2012,01,01) as StartDate,

makedate(2012,2,01) as EndDate

autogenerate (1) ;

• ###### Re: How Do I Get the Number of Months Between Two Dates?

February 29th to April 1st is 32 days, but two months.  Your calculation would give 1.05, which would round to one.  You cannot calculate the number of months between two days with the number of days between them, just as you cannot calculate the number of days between two dates when given the month and day alone.

• ###### Re: How Do I Get the Number of Months Between Two Dates?

In that case I would use monthstart for both dates

• ###### Re: How Do I Get the Number of Months Between Two Dates?

That's close, but it's still an approximation.  A quick test shows that from 2000/03 to 2029/02 is 347 months, but that math gives 348: 10,564 days / 30.4 = 347.5.  That assumes that the rounding function used rounds 0.5 up; if it rounds down, there will be other errors.  Using the more exact figure of 30.438333 helps, but I'd be more comfortable using a calculation which is algorithmically correct rather than risk getting incorrect results at some point.

• ###### Re: How Do I Get the Number of Months Between Two Dates?

Here's a somewhat more compact version of my previous solution allowing an oprional second parm using ALT.

SET MonthDiff=

(

(year(alt(\$2,today(2)))*12) + (month(alt(\$2,today(2))))

)

- ((year(\$1)*12)+month(\$1));

-Rob

• ###### Re: How Do I Get the Number of Months Between Two Dates?

Excellent

On Feb 22, 2012 12:14 AM, "Rob Wunderlich" <

• ###### Re: How Do I Get the Number of Months Between Two Dates?

Is there a way to accommodate two date fields with this solution ?

I.e. StartDate - EndDate  ?

• ###### Re: How Do I Get the Number of Months Between Two Dates?

Look at the entry I left on your original post.