Business Functions Library for Excel

      1. Getting Started
      2. Using The Help File
      3. Top Dozen Functions
      4. Golden Rules
      5. Excel 2007
      1. Go To
      2. Function Selector
      3. CalculateFull
      4. Calculate WorkSheet
      5. Trace Facility
      6. Quick Paste Example
      7. Tutorials
      8. Function Finder
      9. Examples
      10. Help
      11. Excel"s Function Wizard
      12. Access Internet
      13. Usage of Functions (Audit)
      14. Uninstall
      1. Time Periods
      2. Inclusive and Exclusive Dates
      3. Using Daycount
      4. Examples of DayCount
      5. Annual Date Sequences
      6. ProjMode and Inclusive Dates
      7. Date Rolling Convention
      1. Rate Projections Functions Walkthrough
      2. Accruals and Cash
      3. Repeating Formulae
      4. Range Names and References
      5. Optional Parameters
      6. Using PmtsPerYear
      7. Modelling Seasonality
      8. Calculating Interest
      9. Using Business Functions in VBA (Visual Basic for Applications)
      10. Array Functions
      11. Volatility
      12. Annual Equivalent Rates
      13. Array Function
      14. Auto Multi Functions
      15. Variable Plurality
      16. GoalSeek
      1. Introduction To the Worked Examples
      2. Daycount
      3. General Projections
      4. Business Plans
      5. Cashbasis And Periods
      6. Using Timebases
      7. Using Dates
      1. How To Generate a time scale for a structured financing
      2. How To Project Rents off a Rental Forecast
      3. How To Run a model on different time bases
      4. How To Isolate The Cause of a Errors in Cells using Trace
      1. Introduction to the Utilities
      2. Audit
      3. Synchronized Range Insert/Delete
      4. Database Edit
      5. Insert Macro Button
      6. Link Analyser
      7. Range Describer
      8. PrintScript (beta)
      9. Create Local Range Name
      1. Number Formats
      2. Apply BF"s Color Palette
      3. Bullets
      4. Color Cells
      5. Conditional Formats
      1. Validation DropDowms
      2. Validation Standards
      1. Select Excel Function
      2. Array Function Tools
      3. Sort Sheets
      4. Range Value
      5. Named Range Manager
      6. Enforce WorkBook Settings
      7. Monte-Carlo
      8. TimeChart
      1. The ".ini" file
      1. Forum
      2. Online Help
      3. Templates
      1. Conversion of Input Strings to Values
      2. List of Holidays
      3. Acknowledgements and Trademarks
      4. Published Editions Changes in Behaviour
      5. Range Handling And Constraints
      6. Dates in Excel and Business Functions
      7. Old Composite DayCount Format
      8. DayCount in Excels"s Functions
      1. NPV of Annual To Periodic CashFlows - CorrectionM
      2. Interest - Simple, Annual, Continous and Discount Factors
      1. New Functions
      2. Obsolete Functions
      3. Discontinued Functions
      4. Deprecated Functions
    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
    Business Functions Date Numbers
    Dates1 Jan 19101 Jan 3655
    Date Numbers3654641003
    Business Functions Decimal Years
    Dates
    Decimal Year19103653


    Business Functions Date Input Examples


    Input Value
    (Date Number or Decimal Year)
    How Business Functions Interprets
    This Value


    1909.00Illegal (error will result)
    1910.00Decimal Year
    3652.00Decimal Year
    3653.00Date Number
    3654.00 Date Number
    641003.00Date Number
    641004.00Illegal (error will result)

Business Functions phone: +44 (0)20 8144 9374 (London UK)
Website Design: Webpure