Days360

[stephencredmond]

Recently a client asked me about the Excel function Days360. This is a commonly used function within the financial industry to calculate the number of days between two dates based on a 360 day year - i.e. 12 x 30day months.

The function in Excel takes 2 mandatory parameters and 1 optional parameter:

DAYS360(start_date,end_date,method)

If the "method" parameter is FALSE or left out, then it uses the US (NASD) method. If it is TRUE then it uses the European method. The difference is that in the European method if either the start or end date are on the 31st of the month, they are considered to be on the 30th of the month.

I have a simple VBScript function that replicates this functionality:

Function Days360(StartDate, EndDate, European)

Dim Days1, Days2
Days1 = Day(EndDate)
Days2 = Day(StartDate)

If European and Days1 = 31 Then Days1 = 30
If European and Days2 = 31 Then Days2 = 30

Days360 = DATEDIFF("m", StartDate, EndDate) * 30 + Days1 - Days2

End Function

Unfortunately VBScript doesn't have a MIN function or this could be reduced to be even simpler.

Now, if I define ths VBScript function in my QV Document, I can call it from within the load script in a QlikView application. Since version 8.2 though, I am unable to call macro functions from the chart so I have to have a different solution.

Oleg gave a good formula for the month difference: http://community.qlik.com/forums/p/9923/39894.aspx#39894

MonthDiff = (year(Date1)*12 + Month(Date1)) - (year(Date2)*12 + Month(Date2))

This works great for me. So, I now just need to add the days subtraction. This needs to be two separate functions, one for US and one for Europe.

US:

=(((YEAR(Date2)*12 + MONTH(Date2)) - (YEAR(Date1)*12 + MONTH(Date1))) * 30) + DAY(Date2) - DAY(Date1)

Europe (using the Nummin function to step back a 31st to 30th):

=(((YEAR(Date2)*12 + MONTH(Date2)) - (YEAR(Date1)*12 + MONTH(Date1))) * 30) + Nummin(DAY(Date2),30) - Nummin(DAY(Date1),30)

I hope that this helps others get past this one.

It is worth remembering. No matter what function that you come across that doesn't exist in QlikView, it has to be calcuable some way - and probably is do-able within QlikView.

Stephen

[winzbaba]

This post over 10 years old and I found it useful while working on something similar. Thank you!