Difference between revisions of "Date Functions"

m (MakeTime(h,m,s))
(DateAdd(date, offset, unit))
Line 118: Line 118:
===DateAdd(date, offset, unit)===
===DateAdd(date, offset, unit)===
Gives a date value that is '''offset''' years, months, or days from '''date''', according to whether '''unit''' is "Y", "M", "D", or "WD".   
Gives a date value that is '''offset''' years, months, or days from '''date''', according to whether '''unit''' is "Y", "Q", "M", "D", or "WD".   
(new to 4.1) DateAdd can also add time increments in hours, minutes or days using a ''unit'' of "h", "m" or "d".
(new to 4.1) DateAdd can also add time increments in hours, minutes or days using a ''unit'' of "h", "m" or "d".
Line 139: Line 139:
Use a negative ''offset'' to subtract units from a date.
Use a negative ''offset'' to subtract units from a date.
;Expects: '''date''' and '''offset''' to be numbers, and '''unit''' to be 'Y', 'M', 'D', or 'WD' (upper or lowercase).
;Expects: '''date''' and '''offset''' to be numbers, and '''unit''' to be 'Y', 'Q', 'M', 'D', 'WD', 'h', 'm', or 's' (upper or lowercase, except for 'M' and 'm' which are case sensitive).
;[[Syntax:]] DateAdd(date, offset: Number; unit: Text)
;[[Syntax:]] DateAdd(date, offset: Number; unit: Text)

Revision as of 18:02, 27 January 2010

What's new in Analytica 4.0? >

What's new in Analytica 4.1? >

Analytica 4.0 provides much greater flexibility for formatting dates -- in US and ROW (rest of the world) formats. It also adds four useful functions for computing with dates.

Date formats

You can control whether and how a number is displayed as a date in the Number Format dialog, available from the Result menu:

Number format.png

The Date format in the Number Format dialog offers these options:

Short -- e.g., 8/5/2006
Abbrev -- e.g., Aug-5-2006
Long -- e.g., Thursday, 05 August, 2006
Full -- e.g., "5-Aug-2006 3:50:21 PM" { introduced in release 4.2.3 }

The actual formats for the above dates depend on settings in the Regional and Language Options from the Windows Control Panel. See below for details.

Custom -- lets you select an existing custom format or make a new one. These are some examples
dd-MM-yy → 05-8-06
'Q'Q YYYY → Q2 2006
www, d MMM yyyy → Thu, 5 Aug 2006
wwww, d of MMMM, yyyy → Thursday, 5 of August, 2006
d-MMM-yyyy hh:mm:ss tt → 5 Aug 2006 05:45:00 PM { "hh:mm:ss tt" requires 4.1 }

The Custom date format uses these letter codes (standard conventions from Microsoft Windows):

d: numerical day of the month -- 1, 2,... 31
dd: numeric day of the month with two digits -- 01, 02,...31
ddd: abbreviated ordinal day of month - 1st, 2nd, ..., 31st
dddd: ordinal day of month -- first, second, ..., thirty-first
Dddd: capitalized ordinal day of month -- First, Second, ... Thirty-first
www: weekday in three letters -- Mon, Tue,.. Sun
wwww: weekday in full -- as Monday, Tuesday, ... Sunday
M: month as a number -- 1, 2, ... 12
MM: month as two-digit number -- 01, 02, ...12
MMM: month as three letter name -- Jan, Feb, ... Dec
MMMM: month as full name -- January, February, ... December
q: quarter as one digit -- 1, 2, 3, 4
yy: year as two digits -- e.g., 99, 00, 01
yyyy: year as four digits -- e.g., 1999, 2000, 2001
Any other characters, including space, appear literally as given.
To show a character literally that is also a code, enclose it in quotes, e.g. 'q'q → q2

If you specify any date format for an input variable or Edit table, you can enter dates in any acceptable date format -- no matter which date format is specified. For example, a variable with a date format, interprets "9/11/2001" as "11 September, 2001" on a computer set to USA region or "9 November, 2001" elsewhere. With no date format, it interprets "9/11/2001"as ((9 divided by 11) divided by 2001) = 0.000409!

Regional and Language settings

The names of days and months and the formats used for Short, Abbrev and Long dates depend on the regional settings for Windows. So, a user in the US may see a short date as 9/11/2001, while a user in Denmark may see 11/9/2001. You can review the settings in Regional and Language Options from the Windows Control Panel. To modify them, cick Customize button and select teh Date tab or Languages tab. For example, if you set the language to Spanish (Argentina), Makedate(2007, 2, 3) with the Long date setting displays as:

Sábado, 03 de Febrero de 2007.

Date values and the date origin

Analytica represents a date as a date value -- that is, the number of days since the date origin. By default the date origin is Jan 1, 1904 -- as used by most Macintosh applications, including Excel on Macintosh, and all releases of Analytica on Macintosh and Windows up to Analytica 3.1. If you check Use Excel date origin in the Preferences... dialog, the date origin is Jan 1, 1900 -- as used in Excel on Windows (unless reset) and most other Windows software.

With 'Use Excel date origin checked, the numeric value of dates are the same in Analytica and Excel for Windows for dates falling on or after 1 Mar 1900. Because of a bug in Excel, in which Excel incorrectly treats Feb 29, 1900 is a valid day (1900 was not really a leap year), dates falling before that date will not have the same numeric index in Analytica as they do in Excel.

The integer portion of the date value represents the number of days since the date origin. The fractional portion, if present, represents the time as a fraction of a 24-hour day starting at midnight. The time portion is utilized in release 4.1. Excel utilizes the time fraction in the same manner.

When using models containing dates or date functions from Analytica releases 3.1 or earlier, you should keep Use Excel date origin unchecked. If you want to paste or link values from Excel for Windows or other Windows software to or from Analytica, you should check this option.

Analytica can handle dates from 1 AD to well beyond 9999 AD. Dates earlier than the date origin are represented as negative integers. Dates use the Gregorian calendar: Years divisible by 4 are leap years, except those divisible by 100 which are not leap years, except those divisible by 400 which are leap years.

You can simply add an integer n to a date to get the date n days ahead.

MakeDate(year, month, day)

Gives the date value for the date with that year, month, and day. If omitted, month and day default to 1.

MakeDate(2007,5,15) → 15-May-2007
MakeDate(2000) → 1-Jan-2000
year, month, and date should be positive integers, or coercible to positive numbers.
MakeDate(year: Coerce Positive; month, day: Optional Coerce Positive)
Special functions


Gives the time of day as an elapsed fraction of the day, i.e., a number between 0 and 0.99999. The allowed range on the parameters are: 0<=h<=23, 0<=m<=59 and 0<=s<=59, with the exception that h can exceed 23 when encoding a duration of more than a day, in which case the result will be greater than 1.

MakeTime(15,30,00) → 0.645833333   { 03:30:00 PM }

DatePart(date, part)

Gives the year, month, or day as a number, given a date value date. When part='Y', it gives the four digit year as a number, such as 2006. When part='M', it gives a number between 1 and 12. When part='D', it gives a number between 1 and 31. When part='w', it returns a number from between 1 (Sunday) and 7 (Saturday).

DatePart(MakeDate(2006, 2, 28), 'D') -> 28
date as a date number, and part as 'Y', 'M', or 'D' (upper- or lowercase, by 'M' must be upper).
DatePart(date: Numeric; part: Text)
Special functions
Less Common Options
part may also be: 'MMM' (returns e.g., 'Jan'), 'MMMM' (e.g., 'January'), 'ddd' (e.g., '1st'), 'dddd' (e.g., 'first'), 'Dddd' (e.g., 'First'), 'w' (e.g., 1 for Sunday, 2 for Monday, ..., 7 for Saturday), 'www' (e.g., 'Mon'), 'wwww' (e.g,. 'Monday'), 'q' (1 to 4, e.g., 1 for Jan-Mar).
Time components (requires 4.1)
'H' (hour 0 to 23), 'h' (hour 1 to 12), 'm' (minute 0 to 59}, 's' (second 0 to 59), 'HH', 'hh', 'mm', 'ss' (two digit text, e.g., "03"), ' H', ' h', ' m', ' s', 't', 'tt' ("AM" or "PM").
Elapsed days (requires 4.1)
'wd', 'wd+', 'wd-' (number of weekdays since the date origin. 'wd' and 'wd+' counts the indicated day, 'wd-' does not count the indicated day), '#d' (day number in current year), '#w' (week number in current year, week starts on Sunday), '#wm' (week number in current year, week starts on Monday), 'e#w' and 'e#wm' (European convention, week number in current year, week 1 is the first week with at least 3 days, with 'e#w' week starts on Sunday, with 'e#wm' week starts on Monday).
More Examples and Tips

This makes a sequence of weekdays landing between date1 and date2:

Index J:=date1..date2 do Subset( DatePart(J,"w")>=2 and DatePart(J,"w")<=6 )

This computes the number of weekdays between day1 and day2, including day1 and day2 (if day1 or day2 land are weekdays). When day2<day1 the result is negative. The example requires 4.1:

DatePart(day2,"wd+") - DatePart(day1,"wd-")

DateAdd(date, offset, unit)

Gives a date value that is offset years, months, or days from date, according to whether unit is "Y", "Q", "M", "D", or "WD".

(new to 4.1) DateAdd can also add time increments in hours, minutes or days using a unit of "h", "m" or "d".


DateAdd is especially useful for generating sequences of dates for a time index:

DateAdd(MakeDate(2006,1,1), 0..12, "M") 
-> ["1 Jan 2006", "1 Feb 2006", "1 Mar 2006", ... "1 Jan 2007"]

(note: the actual values in the list returned above are numbers formatted as dates)

If an offset would appear to go past the end of a month, such as

DateAdd( MakeDate(2004, 2, 29), 1, 'Y' )  -> 2005-Feb-28
DateAdd( MakeDate(2006, 10, 31), 1, 'M' ) -> 2006-Nov-30

it returns the last day of the month. In the first example, the date 2005-Feb-29 does not exist, and in the second example the date 2006-Nov-31 doesn't exist, so in each case, the last day of the month is returned.

Adding a day offset, DateAdd(date, n,"D"), is equivalent to date+n, since date is represented as an integer. DateAdd(date,n,"WD") adds the specified number of weekdays to the first weekday equal to or falling after date.

Use a negative offset to subtract units from a date.

date and offset to be numbers, and unit to be 'Y', 'Q', 'M', 'D', 'WD', 'h', 'm', or 's' (upper or lowercase, except for 'M' and 'm' which are case sensitive).
Syntax: DateAdd(date, offset
Number; unit: Text)
Special functions
More examples and tips
MakeDate( 2007, 2, 10)                   --> Saturday, 2007-Feb-10
DateAdd( MakeDate(2007,2,10), 0,  "WD" ) --> Monday, 2007-Feb-12
DateAdd( MakeDate(2007,2,10), -1, "WD" ) --> Friday, 2007-Feb-9
DateAdd( MakeDate(2007,2,10),365, "D" )  --> Sunday, 2008-Feb-10
MakeDate(2007,2,10) + 365                --> Sunday, 2008-Feb-10
DateAdd( MakeDate(2007,2,10),365, "WD" ) --> Monday, 2008-July-7

The following generates a series of dates landing on the 1st and 15st of each month:

var J := 0..24;
DateAdd( MakeDate(2006,1, if Mod(J,2)=0 Then 1 Else 15), floor(J/2), "M")
[ 2006-Jan-1, 2006-Jan-15, 2006-Feb-1, 2006-Feb-15, 2006-Mar-1, ..., 2007-Jan-1 ]


Returns the date number for the day on which the function is evaluated. Unlike other functions, it gives a different value every day the model is run.


In Analytia 4.1 or later, Today accepts optional flags. Setting WithTime to true includes the current time of day in the fractional part of the result, and utc returns the date and time in Universal Coordinated Time (aka Grenwich mean time). When utc is false or omitted, the time and date in your local time zone is returned.

Beware the result is the date and time at the moment the function is evaluated. The result may be cached with the results of your model and is not invalidated when the date or time changes.

Special functions

You are not allowed to post comments.