XL: DAYS360 Function Produces Different Values Depending on the Version of Excel

ID: Q235575

The information in this article applies to:

Microsoft Excel for Windows 95, version 7.0
Microsoft Excel for Windows, versions 5.0, 5.0c, 4.0, 4.0a
Microsoft Excel for the Macintosh, versions 4.0, 5.0, 5.0a

SYMPTOMS

When you use the DAYS360 function in Microsoft Excel 5.0 and later, the function returns a different value than in Microsoft Excel 4.0.

CAUSE

This behavior occurs because Microsoft changed the default method for the DAYS360 function in Microsoft Excel 5.0 and later.

MORE INFORMATION

The DAYS360 function returns the number of days between two dates based on a 360-day year (twelve 30-day months). This function is usually used to help compute payments if your accounting system is based on twelve 30-day months. In Microsoft Excel 4.0, you cannot change the method that the DAYS360 function uses. In Microsoft Excel 5.0 and later, you can change the method that the DAYS360 function uses, with a new optional parameter.

Microsoft Excel 4.0

In Microsoft Excel 4.0, the DAYS360 function syntax is described below:

DAYS360(start_date, end_date)

Start_date and end_date are the two dates between which you want to know the number of days. Starting dates or ending dates which occur on the 31st of a month become equal to the 30th of the same month. The month February and leap years are handled in the following way: when you use DAYS360 on a non-leap year, the following function


=DAYS360("2/28/93", "3/1/93")

returns 3 days because the DAYS360 function is counting the extra days added to February to give February 30 days. On a leap year, the following function


=DAYS360("2/29/96", "3/1/96")

returns 2 days.

Microsoft Excel 5.0 and Later

In Microsoft Excel 5.0 and later the DAYS360 function adds an optional parameter which sets the method for DAYS360:

DAYS360(start_date, end_date, method)

Start_date and end_date are the two dates between which you want to know the number of days, and method is a logical value that specifies whether to use the U.S. or European method in the calculation. The method parameter defaults to FALSE when omitted, or you can set it to FALSE or TRUE.

Method Set to FALSE or Omitted

When you set the method parameter to FALSE or omit it, the DAYS360 function uses the U.S. (NASD) method. If the starting date is the 31st of a month, it becomes equal to the 30th of the same month. If the ending date is the 31st of a month and the starting date is earlier than the 30th of a month, the ending date becomes equal to the 1st of the next month, otherwise the ending date becomes equal to the 30th of the same month. The month February and leap years are handled in the following way: on a non-leap year the following function


=DAYS360("2/28/93", "3/1/93", FALSE)

returns 1 day because the DAYS360 function ignores the extra days added to February. On a leap year the following function


=DAYS360("2/29/96","3/1/96", FALSE)

returns 1 day for the same reason.

Method Set to TRUE

When you set the method parameter to TRUE, the DAYS360 function uses the European method. Starting dates or ending dates that occur on the 31st of a month become equal to the 30th of the same month. Microsoft Excel 4.0 also uses this same method. The month February and leap years are handled in the following way: on a non-leap year the following function


=DAYS360("2/28/93", "3/1/93", TRUE)

returns 3 days because the DAYS360 function is counting the extra days added to February to give February 30 days. On a leap year the following function


=DAYS360("2/29/96", "3/1/96", TRUE)

returns 2 days for the same reason.

Additional query words:

Keywords : kbdta
Version : MACINTOSH:4.0,5.0,5.0a; WINDOWS:4.0,4.0a,5.0,5.0c,7.0
Platform : MACINTOSH WINDOWS
Issue type : kbprb