function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Mildred Morales 19Mildred Morales 19 

360DAYS Method for calculations

Hi all! 

I am trying to replicate the excel Days360 function which returns the number of days between 2 dates, based on a 360-day year (12 x 30 months).

Does anyone know how to accomplish this?
Best Answer chosen by Mildred Morales 19
Alain CabonAlain Cabon
Hi,

A duration is calculated as an integral number of days between start date A and end date B. The difference in years, months and days are usually calculated separately:

Days360 (A,B) = ( YEAR(B) - YEAR(A) ) x 360+( MONTH (B) - MONTH(A) ) x 30  + ( DAY(B)  - DAY(A) ) ; given that A <= B (approximation)

European and US method don't treat the last day of February in the same way.

European method:
  • If either date A or B falls on the 31st of the month, that date will be changed to the 30th.
  • Where date B falls on the last day of February, the actual date B will be used.
  • All months are considered to last 30 days and hence a full year has 360 days.
US/NASD method: DAYS360  (Excel)
  • 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.
  • All months are considered to last 30 days and hence a full year has 360 days.

Microsoft Excel and StarOffice/OpenOffice.org  DAYS360  = NASD, but not SIA-compliant.

https://en.wikipedia.org/wiki/360-day_calendar

Last day of February is a problem of leap year: https://developer.salesforce.com/docs/atlas.en-us.usefulFormulaFields.meta/usefulFormulaFields/formula_examples_dates.htm
  
OR( 
  MOD( YEAR( date ), 400 ) = 0, 
  AND( 
   MOD( YEAR( date ), 4 ) = 0,
    MOD( YEAR( date ), 100 ) != 0
  )
)

February 29 is a date that usually occurs every four years, and is called leap day. This day is added to the calendar in leap years.

February 28th is not always the last day of February and cannot be changed directly to the 30th without checking the leap year.
 

All Answers

Alain CabonAlain Cabon
Hi,

A duration is calculated as an integral number of days between start date A and end date B. The difference in years, months and days are usually calculated separately:

Days360 (A,B) = ( YEAR(B) - YEAR(A) ) x 360+( MONTH (B) - MONTH(A) ) x 30  + ( DAY(B)  - DAY(A) ) ; given that A <= B (approximation)

European and US method don't treat the last day of February in the same way.

European method:
  • If either date A or B falls on the 31st of the month, that date will be changed to the 30th.
  • Where date B falls on the last day of February, the actual date B will be used.
  • All months are considered to last 30 days and hence a full year has 360 days.
US/NASD method: DAYS360  (Excel)
  • 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.
  • All months are considered to last 30 days and hence a full year has 360 days.

Microsoft Excel and StarOffice/OpenOffice.org  DAYS360  = NASD, but not SIA-compliant.

https://en.wikipedia.org/wiki/360-day_calendar

Last day of February is a problem of leap year: https://developer.salesforce.com/docs/atlas.en-us.usefulFormulaFields.meta/usefulFormulaFields/formula_examples_dates.htm
  
OR( 
  MOD( YEAR( date ), 400 ) = 0, 
  AND( 
   MOD( YEAR( date ), 4 ) = 0,
    MOD( YEAR( date ), 100 ) != 0
  )
)

February 29 is a date that usually occurs every four years, and is called leap day. This day is added to the calendar in leap years.

February 28th is not always the last day of February and cannot be changed directly to the 30th without checking the leap year.
 
This was selected as the best answer
Mildred Morales 19Mildred Morales 19
Awesome!! It works Thank you sooooo much! I have another project with amortizations. Have you worked on that? LOL
Alain CabonAlain Cabon
It is great news if if that helps. It is also the purpose of this forum (mutual support) and you should be an experienced professional on Salesforce because I didn't post the exact usable formula for a formula field. I didn't worked on that but on many formulas here especially with dates. 
Finding an acceptable solution is often a real brainteaser with the formulas using dates. The final results are sometimes ridiculously complicated and not completely satisfactory but at least, we have tried. 
Alain