65.9K
CodeProject is changing. Read more.
Home

Difference between dates using a 360 day calendar

ErnestoNet
starIconstarIconstarIconstarIconstarIcon

5.00/5 (1 vote)

Mar 4, 2013

CPOL

2 min read

viewsIcon

23781

downloadIcon

683

DAYS360 excel function

Imagen

Introduction

Many financial calculations between dates require that days are calculated using a 30 days month (360 days year)
You can find additional information in wikipedia here To do that, there is an excel function Days360. Unfortunately, it requires that your application includes excel libraries.

This application implements C# code that does that calculation and test it against excel.

Problem Logic

There are two cases to consider:
  • When the month and year of both dates are the same.
  • When the month and year are different.
For the first case, the calculation is simple, day of DT minus day of DF. For the seconde case, we can divide the calculation in three sections, considering Date From (DF) and Date To (DT)
  • From the day of DF to 30 of the same month (30 - Day(DF))
  • Complete months of 30 days
  • From 1 to the day of DT of the month of DT
For example, for the dates 10/01/2012 and 08/12/2012 In this case, the math would be: 20 + (10 * 30) + 8 = 328

Imagen

Special cases

There are special cases to consider when the day from the date from is 31 or the month is February.
For that cases, there are two methods:

The European Method (30E/360):
  • If either date A or B falls on the 31st of the month, that date will be changed to the 30th.
The US/NASD Method (30US/360):
  • If both date A and B fall on the last day of February, then date B will be changed to the 30th.
  • If date A falls on the 31st of a month or last day of February, then date A will be changed to the 30th.
  • If date A falls on the 30th of a month after applying (2) above and date B falls on the 31st of a month, then date B will be changed to the 30th.
This only applies to the day of the date from and the day of the date to. So, the logic would be: - Modify day of date from and day of date to according to special cases - if same month, return day of date to minus day of date from (using modified days). - if not same month, return (30 - day of date from) + Full Months between dates + (day of date to) using modified days.

Improvements

The logic is not optimized for speed. The function returns 0 when the date from is greater than the date to (excel returns negative values).

Source code

The source project is VS2008 and uses Framework 2.0.
It also uses Microsoft.Office.Interop.Excel.dll to validate the function

History

  • 2013-03-04. First version.

References