Date Information family
| Date and Time Calculations category | Home |
This is the most general Dates family, with functions that calculate date values and functions that provide information about a date value.
| Component | What the name means |
|---|---|
| DYear | Determine the decimal year for a date |
| DMonth | Determine the Decimal Month of a date |
| DateYMD | Determine a Date Number given Years,Months,Days |
| DateSeqNum | Convert a Date to an Annual Sequence Number |
| IsSeqDate | Determines if TheDate is one of the specified annual sequence dates |
| DayName | Name of the Day of the Week |
| MonthName | Name of the Month |
| IsLeap | Determine if the year is a leap year |
| DaysInMonth | How many Days in the Month |
| IsAnniversary | Determine if one date is an anniversary of the other |
| WeekNumber | Week Number |
| WeekNumberToDate | Convert a Week Number to a date |
| MonthStart | MonthStart |
| MonthEnd | MonthEnd |
| NumPers | Number of periods per year in an annual sequence |
| MostRecent | Get the most imminent of some range(s) of dates relative to a current date |
| MostImminent | Get the most recent of some range(s) of dates relative to a current date |
| TimeBaseLengths array function | Returns the length of each period in the timebase as an array, in months |
| AnnSeq array function | Generate an annual date sequence |
| CreateTimeBase array function | Create a timebase |
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 Functions
This 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.