Dates in Excel and Business Functions
What a date is, limits, auto date converson
Business Functions uses the same date system as Excel. A date in a cell is actually a date number that can be formatted and displayed by Excel as a date. A date number in Excel is the number of days elapsed since 1 Jan 1900. There are two anachronisms with Excel"s system, that for compatibility reasons carry over into Business Functions:
• 1900 is treated as a leap year when in fact it was not. Actually, only those millennia years that are exactly divisible by 400 are leap years (so 1900 and 2100 are not leap years).
• 1 Jan 1900 is treated as day 1, when you might think it ought to be day zero. (For this reason some authors argue that Excels date system is the number of days elapsed from the mythical date 0 Jan 1900.)
Excel also has the option of a 1904 date system for compatibility with some other applications, but this is not recommended and you should never be tempted to use it.

Recommended way to input a date in a function

The recommended way of inputting a date into a function, in either Excel or Business Functions, is to first type the date in a cell, e.g. 1/1/2000 and then refer to that cell in the function argument list. What actually happens is that Excel converts your date 1/1/2000 into a date number (in this case 36526), and if you format the date cell as a number you can see this. If you format it as date, you will of course see the date, even though Excel internally still views that date as a number. That is why, if you want to add one day to a number, you simply enter =D4+1, if the date was in cell D4.

The reason that using a separate cell is the best way to input a date is because you cannot, in an Excel function, simply enter 1/1/2000 - it will get interpreted as 1 divided by 2000 and is a nasty trap. You CAN apparently and occasionally enter a date as text in an Excel function e.g. "1/1/2000" but this is NOT recommended and is NOT possible with Business Functions. Be very careful about inadvertently inputting dates as text.

The reason text input is not a good idea is because it is in practise somewhat unpredictable and therefore unreliable, and depends on the date format in your national locale. Another reason is that it encourages you to enter dates as text in a cell, which leads to real confusion. Business Functions simply will NOT accept a date as text.

So we always recommend having the date in its own cell, and then referring to that cell in a function, whether it be an Excel function or a Business Functions function.

Auto Date Conversion in Business Functions

Business Functions has a handy alternative way of entering dates, which is simply to enter the decimal year. So instead of entering 1-Jul-2000 in a cell and referring to that cell, you can use the number 2000.5, either in a referred cell, or more usefully, directly in the function itself. Decimal years are quite intuitive:

DateDecimal Year

1 Jul 20002000.50
1 April 20002000.25
1 Jan 20002000
1 May 20002000.3334 (better input as 2000 + 4/12, as recurring numbers can"t be input directly)
16 March 20002000+2.5/12

Limits

There are limits on the minimum and maximum dates that are acceptable. Business Functions has narrower limits than Excel, but still allows for all dates between 1 Jan 1910 and 1 Jan 3655. Excel itself goes from 1 Jan 1900 to 31 Dec 9999.

MinimumMaximum

Excel Date Numbers
Dates1 Jan 190031 Dec 9999
Date Numbers (1900 Date System)12958465
Dates1 Jan 19101 Jan 3655
Date Numbers3654641003
Dates
Decimal Year19103653

Input Value
(Date Number or Decimal Year)