> -
- Rate Projections Functions Walkthrough
- Accruals and Cash
- Repeating Formulae
- Range Names and References
- Optional Parameters
- Using PmtsPerYear
- Modelling Seasonality
- Calculating Interest
- Using Business Functions in VBA (Visual Basic for Applications)
- Array Functions
- Volatility
- Annual Equivalent Rates
- Array Function
- Auto Multi Functions
- Variable Plurality
- GoalSeek
- 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.)
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: 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:
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.
Business Functions Date Input Examples
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||