Click here to Skip to main content
Click here to Skip to main content

Tagged as

Difference between dates using a 360 day calendar

, 4 Mar 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

ErnestoNet
Software Developer
Argentina Argentina
System developer from Argentina.
 
Programmed in VB 5,6,.NET, C#, Java, PL-SQL, Transac-SQL, C, C++ and even some "calculator" language.
 
Love to build small, useful applications.
Usually building big and complicated apps based on solid, reliable components.
 
Hobbies: reading, photography, chess, paddle, running.

Comments and Discussions

 
BugDon't think this calculation is correct Pinmemberfloele24-Nov-13 23:18 
GeneralRe: Don't think this calculation is correct PinmemberErnestoNet26-Nov-13 9:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.1411023.1 | Last Updated 4 Mar 2013
Article Copyright 2013 by ErnestoNet
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid