Business Functions Library for Excel

 Date Information family Functions that calculate dates or provide information about dates Description: This is the most general Dates family, with functions that calculate date values and functions that provide information about a date value. Introduction: This family of date functions deals with:Information about dates, for example DayName, MonthName and IsLeap.Ways to represent dates, like DYear, DateYMD, DMonth and DateNum.Simple Month calculations. for example determining the last day in the month (MonthEnd) or the days in the month DaysInMonth.Week Number calculations including WeekNumber, which will convert a date to either an ISO week number or an Excel WEEKNUM week number (the basis is different), and WeekNumberToDate, a rather unique function which does the inverse, ie converts a week number to a date.Finding dates close to other dates, such as MostImminent and MostRecent.About Dates in Business FunctionsThis family is the first and introductory family of the Date and Time Calculations category, and some of its functions touch on how dates are stored, used and represented in Excel and Business Functions. Here are some key points:A date is the number of days elapsed since 1/1/1900, where 1/1/1900 is deemed to be date number 1 and the year 1900 is assumed by Excel (erroneously, as it turns out) to have 365 days. There is another date system called the 1904 date system that is not recommended.Business Functions stores a date in a number of ways. It actually stores the date number as well as the year, month and day (which it remembers), and a thing called a decimal month. It stores the decimal month (the fraction of months elapsed since 1900 where fractions of a month are treated as DaysElapsed/DaysInMonth because that turns out to be useful in calculations and is the default daycount method in BF. A date is actually the instant after midnight on the day in question ie first thing in the early morning. Its important to remember this when dealing with dates - that a date is not really a day but a very precise point in time at the very start of the day. In Excel, 6am on a 1 Jan 2000 would be represented as 36526.25, which tells you how Excel deals with times. BF, on the other hand, does not consider times at all - it just has 1 Jan 2000 as 36526 and 2 Jan as 36427.Business Functions uses Annual Date Sequences a lot to describe annually recurring dates such as are used in Periods variable. An annual date sequence is just a range (or array) of a few numbers in the format mm.dd (month.day), for example {01.01, 07.01} describes 1st Jan, 1st July. One of the functions in this family, DateSeqNum, actually creates a number in this format, given a date, so 1 July becomes 07.01. Functions in the Date Information family (20)DYear Determine the decimal year for a dateDMonth Determine the Decimal Month of a dateDateYMD Determine a Date Number given Years,Months,DaysDateSeqNum Convert a Date to an Annual Sequence NumberIsSeqDate Determines if TheDate is one of the specified annual sequence datesDayName Name of the Day of the WeekMonthName Name of the MonthIsLeap Determine if the year is a leap yearDaysInMonth How many Days in the MonthIsAnniversary Determine if one date is an anniversary of the otherWeekNumber Week NumberWeekNumberToDate Convert a Week Number to a dateMonthStart MonthStartMonthEnd MonthEndNumPers Number of periods per year in an annual sequenceMostRecent Get the most imminent of some range(s) of dates relative to a current dateMostImminent Get the most recent of some range(s) of dates relative to a current dateTimeBaseLengths Returns the length of each period in the timebase as an array, in monthsAnnSeq Generate an annual date sequenceCreateTimeBase Create a timebase

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