Difference between revisions of "Date Functions"

(ER 20325 'Date' and 'Time' options)
 
(14 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 
[[Category:Date and Time Functions]]
 
[[Category:Date and Time Functions]]
[[Category:Doc Status C]] <!-- For Lumina use, do not change -->
+
[[Category: Date formats]]
A date in Analytica is represented as the number of days since Jan 1, 1904 (or 1900), as in Excel. You can enter or display a date in almost any format -- using US conventions or ROW (Rest Of World) using [[#Regional and Language settings | Regional and Language settings]]. A time is expressed as a fraction of a day. You can set the display format for a number as a date in the [[Number format]] dialog. You can create a date or time with [[#MakeDate(year, month, day, valueForInvalid) | MakeDate]]() and [[#MakeTime.28h.2Cm.2Cs.29 | MakeTime]](), get the current date or time with [[Today]](), select an element of a date with [[#DatePart(date, part) | DatePart]](), and add days, weeks, months, or years to a date with [[#DateAdd(date, offset, unit) | DateAdd]](). See below for details.
+
  
===Date formats===
+
{{ReleaseBar}}
  
 +
Analytica represents a date as the number of days since Jan 1, 1904, (or since Jan 1, 1900, if you check use Excel data origin in [[Preferences]]) . You can enter or display a date in almost any format -- using US conventions or ROW (Rest Of World) using [[#Regional and Language settings | Regional and Language settings]]. A time is expressed as a fraction of a day. You can set the display format for a number as a date or time in the [[Number format]] dialog. You can create a date or time with [[#MakeDate(year, month, day, valueForInvalid) | MakeDate]]() and [[#MakeTime.28h.2Cm.2Cs.29 | MakeTime]](), get the current date or time with [[Today]](), select an element of a date with [[#DatePart(date, part) | DatePart]]().  [[#DateAdd(date, offset, unit) | DateAdd]]() lets you add a hour, day, month, or other interval to a date. It is also useful for creating an Index of dates or times.
 +
 +
__TOC__
 +
 +
===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:
 
You can control whether and how a number is displayed as a date in the '''Number Format''' dialog, available from the '''Result''' menu:
  
[[Image:Number format.png|400px]]
+
:[[Image:Number format.png|400px]]
  
 
The '''Date''' format in the '''Number Format''' dialog offers these options:
 
The '''Date''' format in the '''Number Format''' dialog offers these options:
 +
:<code>Short</code> -- e.g., 8/5/2006
 +
:<code>Abbrev</code> -- e.g., Aug-5-2006
 +
:<code>Long</code> -- e.g., Thursday, 05 August, 2006
 +
:<code>Full</code> -- e.g., 5-Aug-2006 3:50:21 PM   
 +
::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.
 +
:<code>Custom</code> -- lets you select an existing custom format or make a new one. Some examples:
 +
::<code>dd-MM-yy</code> -- e.g.,  05-8-06
 +
::<code>'Q'Q YYYY</code> -- e.g.,  Q2 2006
 +
::<code>www, d MMM yyyy</code> -- e.g., Thu, 5 Aug 2006
 +
::<code>wwww, d of MMMM, yyyy</code> -- e.g.,  Thursday, 5 of August, 2006
 +
::<code>d-MMM-yyyy hh:mm:ss tt</code> -- e.g., 5 Aug 2006 05:45:00 PM
  
:'''Short''' -- e.g., 8/5/2006
+
The <code>Custom</code> date format uses these letter codes (standard conventions from Microsoft Windows):
:'''Abbrev''' -- e.g., Aug-5-2006
+
::<code>d</code>: numeric day of the month -- <code>1, 2,... 31</code>
:'''Long''' -- e.g., Thursday, 05 August, 2006
+
::<code>dd</code>: numeric day of the month with two digits -- <code>01, 02,...31</code>
:'''Full''' -- e.g., "5-Aug-2006 3:50:21 PM"   
+
::<code>ddd</code>: abbreviated ordinal day of month - <code>1st, 2nd, ..., 31st</code>
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.
+
::<code>dddd</code>: ordinal day of month -- <code>first, second, ..., thirty-first</code>
:'''Custom''' -- lets you select an existing custom format or make a new one. These are some examples
+
::<code>Dddd</code>: capitalized ordinal day of month --<code> First, Second, ... Thirty-first</code>
::dd-MM-yy &rarr;  05-8-06
+
::<code>www</code>: weekday in three letters -- <code>Mon, Tue,.. Sun</code>
::'Q'Q YYYY  &rarr;  Q2 2006
+
::<code>wwww</code>: weekday in full -- as <code>Monday, Tuesday, ... Sunday</code>
::www, d MMM yyyy &rarr;  Thu, 5 Aug 2006
+
::<code>M</code>: month as a number -- <code>1, 2, ... 12</code>
::wwww, d of MMMM, yyyy &rarr;  Thursday, 5 of August, 2006
+
::<code>MM</code>: month as two-digit number -- <code>01, 02, ...12</code>
::d-MMM-yyyy hh:mm:ss tt &rarr; 5 Aug 2006 05:45:00 PM 
+
::<code>MMM</code>: month as three letter name --<code> Jan, Feb, ... Dec</code>
 
+
::<code>MMMM</code>: month as full name -- <code>January, February, ... December</code>
The Custom date format uses these letter codes (standard conventions from Microsoft Windows):
+
::<code>q</code>: quarter as one digit -- <code>1, 2, 3, 4</code>
::d: numerical day of the month -- 1, 2,... 31
+
::<code>yy</code>: year as two digits -- e.g., <code>99, 00, 01</code>
::dd: numeric day of the month with two digits -- 01, 02,...31
+
::<code>yyyy</code>: year as four digits -- e.g., <code>1999, 2000, 2001</code>
::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.
 
::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 &rarr; q2
+
::To show a character literally that is also a code, enclose it in quotes, e.g. <code>'q'q &rarr; q2</code>
  
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!
+
If you set a date format for an input variable or Edit table, you can enter dates in any acceptable date format -- no matter what date format was specified.  For example, a variable with a date format interprets <code>"9/11/2001"</code> as "11 September, 2001" on a computer set to the USA region, or "9 November, 2001" elsewhere. With no date format, it interprets <code>"9/11/2001"</code> as ((''9''  divided by ''11'') divided by ''2001'') = 0.000409!
  
 
=== Regional and Language settings===
 
=== 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, click '''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:  
+
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<code> 9/11/2001</code>, while a user in Denmark may see <code>11/9/2001</code>.  You can review the settings in '''Regional and Language Options''' from the Windows '''Control Panel'''. To modify them, click '''Customize''' button and select teh '''Date''' tab or '''Languages''' tab.  For example, if you set the language to ''Spanish (Argentina)'', <code>Makedate(2007, 2, 3)</code> with the '''Long''' date setting displays as:  
 
:Sábado, 03 de Febrero de 2007.
 
:Sábado, 03 de Febrero de 2007.
  
 
===Date values and the date origin===
 
===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.1If 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.  
+
Analytica represents a date as a ''date value'' -- that is, the number of days since the ''date origin''. The default date origin is Jan 1, 1904 -- as used by most Macintosh applications, including Excel on Macintosh.  To can change the date origin to Jan 1, ''1900'' -- the default in   Excel on Windows and most other Windows software -- open the '[[Preferences]] dialog and check '''Use Excel date origin'''.  
  
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.   
+
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 Excel incorrectly treats Feb 29, 1900 as 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.
 
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.
Line 66: Line 69:
 
Gives the date value for the date with that «year», «month», and «day». If omitted, «month» and «day» default to 1.   
 
Gives the date value for the date with that «year», «month», and «day». If omitted, «month» and «day» default to 1.   
  
''new to [[Analytica 4.7]]'' The optional «valueForInvalid» specifies a value to return when there is no date having the given «year», «month» and «day». When this parameter is omitted, the closest valid date is returned, is the same month requested when possible.  
+
''new to [[Analytica 5.0]]'' The optional «valueForInvalid» specifies a value to return when there is no date having the given «year», «month» and «day». When this parameter is omitted, it returns the closest valid date in the same month when possible.  
  
 
;Examples:
 
;Examples:
MakeDate(2007,5,15) &rarr; 15-May-2007
+
:<code>MakeDate(2007, 5, 15) &rarr; 15-May-2007</code>
MakeDate(2000) &rarr; 1-Jan-2000
+
:<code>MakeDate(2000) &rarr; 1-Jan-2000</code>
MakeDate(2015,2,29,valueForInvalid:null) &rarr; [[Null]]
+
:<code>MakeDate(2015, 2, 29, valueForInvalid: null) &rarr; Null</code>
MakeDate(2015,2,29) &rarr; 28-Feb-2015
+
:<code>MakeDate(2015, 2, 29) &rarr; 28-Feb-2015</code>
  
 
;Requirements: «year», «month», and «date» should be positive integers, or coercible to positive numbers.  
 
;Requirements: «year», «month», and «date» should be positive integers, or coercible to positive numbers.  
  
;Library: [[Special functions]]
+
;Library: [[Special_Functions_library|Special functions]]
  
===MakeTime(h,m,s)===
+
===MakeTime(h, m, s)===
  
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.
+
Gives the time of day as a fraction of a day, 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.
  
 
;Examples
 
;Examples
MakeTime(15,30,00) &rarr; 0.645833333  { 03:30:00 PM }
+
:<code>MakeTime(15, 30, 00) &rarr; 0.645833333  { 03:30:00 PM }</code>
  
 
===DatePart(date, part)===
 
===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).
+
Gives the year, month, day, or other part of a date value «date» as a number. When «part» = <code>'Y'</code>, it gives the four digit year as a number, such as 2006.  When «part» = <code>'M'</code>, it gives a number between 1 and 12.  When «part» = <code>'D'</code>, it gives a number between 1 and 31.  When «part»= <code>'w'</code>, it returns a number from between 1 (Sunday) and 7 (Saturday).
  
 
;Example:
 
;Example:
DatePart(MakeDate(2006, 2, 28), 'D') -> 28
+
:<code>DatePart(MakeDate(2006, 2, 28), 'D') &rarr; 28</code>
  
;Expects: '''date''' as a date number, and '''part''' as 'Y', 'M', or 'D' (upper- or lowercase, by 'M' must be upper).
+
;Expects: «date» as a date number, and «part» as <code>'Y', 'M'</code>, or <code>'D'</code> (upper- or lowercase, by <code>'M'</code> must be upper).
  
;[[Syntax]]: DatePart(date: Numeric; part: Text)
+
;Syntax: [[DatePart]](date: Numeric; part: Text)
  
;Library: [[Special functions]]
+
;Library: [[Special_Functions_library|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).
+
;Less Common Options: «part» may also be:  
 +
* <code>'MMM'</code> (returns e.g., 'Jan'),  
 +
*<code>'MMMM'</code> (e.g., 'January'),  
 +
*{{Release|5.0||<code>'EEE'</code> (in English, e.g., 'Jan'),
 +
*<code>'EEEE'</code> (in English, e.g., 'January'),}}
 +
*<code>'ddd'</code> (e.g., '1st'),  
 +
*<code>'dddd'</code> (e.g., 'first'),  
 +
*<code>'Dddd'</code> (e.g., 'First'),  
 +
*<code>'w'</code> (e.g., 1 for Sunday, 2 for Monday, ..., 7 for Saturday),  
 +
*<code>'www'</code> (e.g., 'Mon'),  
 +
*<code>'wwww'</code> (e.g,. 'Monday'),  
 +
*<code>'q'</code> (1 to 4, e.g., 1 for Jan-Mar).{{Release|6.0||
 +
*<code>'date'</code> (the date part, dropping the time part.)}}
  
;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").
+
; Time
 +
*<code>'H'</code> (hour 0 to 23),  
 +
*<code>'h'</code> (hour 1 to 12),  
 +
*<code>'m'</code> (minute 0 to 59),  
 +
*<code>'s'</code> (second 0 to 59),  
 +
*<code>'HH', 'hh', 'mm', 'ss'</code> (two digit text, e.g., "03"),
 +
*<code> 'tt'</code> ("AM" or "PM").{{Release|6.0||
 +
*<code>'time'</code> (the time part only, dropping the date part)}}
  
;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).
+
;Elapsed days
 +
*<code>'wd'</code> number of weekdays since the date origin.   
 +
*<code>'wd'</code> and <code>'wd+'</code> counts the indicated day,  
 +
*<code>'wd-'</code> does not count the indicated day,
 +
*<code> '#d'</code> day number in current year,  
 +
*<code>'#w'</code> week number in current year, week starts on Sunday,  
 +
*<code>'#wm'</code> week number in current year, week starts on Monday,  
 +
*<code>'e#w'</code> and <code>'e#wm'</code> (European convention, week number in current year, week 1 is the first week with at least 3 days, with <code>'e#w'</code> week starts on Sunday, with <code>'e#wm'</code> week starts on Monday).
  
 
;More Examples and Tips:
 
;More Examples and Tips:
  
This makes a sequence of weekdays landing between date1 and date2:
+
This makes a sequence of weekdays between <code>date1</code> and <code>date2</code>:
Index J:=date1..date2 do Subset( DatePart(J, "w") >= 2 and DatePart(J, "w") <= 6 )
+
:<code>Index J:= date1 .. date2 DO Subset(DatePart(J, "w") >= 2 and DatePart(J, "w") <= 6)</code>
  
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:
+
This computes the number of weekdays between <code>day1</code> and <code>day2</code>, including <code>day1</code> and <code>day2</code> (if <code>day1</code> or <code>day2</code> land are weekdays).  When <code>day2</code> < <code>day1</code>,  the result is negative.  
DatePart(day2, "wd+") - DatePart(day1, "wd-")
+
:<code>DatePart(day2, "wd+") - DatePart(day1, "wd-")</code>
  
 
===DateAdd(date, offset, unit)===
 
===DateAdd(date, offset, unit)===
  
Gives a date value that is '''offset''' from '''date''' by '''unit''' "Y" (years), "Q" (quarters) "M" (months) "D" (days), "WD" (weekdays, skipping over Saturdays and Sundays.  It also works for time increments: "h" (hours), "m" (minutes) or "s" (seconds).
+
Gives a date value that is «offset» from «date» by «unit» <code>"Y"</code> (years),<code> "Q"</code> (quarters) <code>"M"</code> (months) <code>"D"</code> (days), <coed>"WD"</code> (weekdays, skipping over Saturdays and Sundays.  It also works for time increments: <code>"h"</code> (hours), <code>"m"</code> (minutes) or <code>"s"</code> (seconds).
  
 
;Examples:
 
;Examples:
DateAdd is especially useful for generating a sequence of dates for a time index:
+
[[DateAdd]]() is especially useful for generating a sequence of dates for a time index:
  
DateAdd(MakeDate(2006, 1, 1), 0..12, "M")  
+
:<code>DateAdd(MakeDate(2006, 1, 1), 0..12, "M") &rarr;</code>
&rarr; [1-Jan-2006, 1-Feb-2006, 1-Mar-2006, ... 1-Jan-2007]
+
:<code> [1-Jan-2006, 1-Feb-2006, 1-Mar-2006, ... 1-Jan-2007]</code>
  
 
Note: the actual display format for the dates depends on what was set in Number format.
 
Note: the actual display format for the dates depends on what was set in Number format.
  
 
If an offset would appear to go past the end of a month, such as
 
If an offset would appear to go past the end of a month, such as
DateAdd(MakeDate(2004, 2, 29), 1, 'Y')  &rarr; 2005-Feb-28
+
:<code>DateAdd(MakeDate(2004, 2, 29), 1, 'Y')  &rarr; 2005-Feb-28</code>
DateAdd(MakeDate(2006, 10, 31), 1, 'M') &rarr; 2006-Nov-30
+
:<code>DateAdd(MakeDate(2006, 10, 31), 1, 'M') &rarr; 2006-Nov-30</code>
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, it returns the last day of the month.
+
  
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'''.
+
it returns the last day of the month. In the first example, the date <code>2005-Feb-29</code> does not exist, and in the second example the date <code>2006-Nov-31</code> doesn't exist, so in each case, it returns the last day of the month.
  
Use a negative ''offset'' to subtract units from a date.
+
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».
  
;Expects: '''date''' and '''offset''' to be numbers, and '''unit''' to be 'Y', 'Q', 'M', 'D', 'WD', 'h', 'm', or 's'. The unit is not case sensitive except for 'M' (months) and 'm' (minutes).
+
Use a negative «offset» to subtract units from a date.
  
;[[Syntax:]] DateAdd(date, offset: Number; unit: Text)
+
;Expects: «date» and «offset» to be numbers, and «unit» to be <code>'Y', 'Q', 'M', 'D', 'WD', 'h', 'm'</code>, or <code>'s'</code>. The unit is not case sensitive except for <code>'M'</code> (months) and <code>'m'</code> (minutes).
  
;Library: [[Special functions]]
+
;Syntax: [[DateAdd]](date, offset: Number; unit: Text)
 +
 
 +
;Library: [[Special_Functions_library|Special functions]]
  
 
; More examples and tips:
 
; More examples and tips:
  
MakeDate(2007, 2, 10)                  &rarr; Saturday, 2007-Feb-10
+
:<code>MakeDate(2007, 2, 10)                  &rarr; Saturday, 2007-Feb-10</code>
DateAdd(MakeDate(2007, 2, 10), 0,  "WD") &rarr; Monday, 2007-Feb-12
+
:<code>DateAdd(MakeDate(2007, 2, 10), 0,  "WD") &rarr; Monday, 2007-Feb-12</code>
DateAdd(MakeDate(2007, 2, 10), -1, "WD") &rarr; Friday, 2007-Feb-9
+
:<code>DateAdd(MakeDate(2007, 2, 10), -1, "WD") &rarr; Friday, 2007-Feb-9</code>
DateAdd(MakeDate(2007, 2, 10),365, "D")  &rarr; Sunday, 2008-Feb-10
+
:<code>DateAdd(MakeDate(2007, 2, 10), 365, "D")  &rarr; Sunday, 2008-Feb-10</code>
MakeDate(2007, 2, 10) + 365              &rarr; Sunday, 2008-Feb-10
+
:<code>MakeDate(2007, 2, 10) + 365              &rarr; Sunday, 2008-Feb-10</code>
DateAdd(MakeDate(2007, 2, 10), 365, "WD") &rarr; Monday, 2008-July-7
+
:<code>DateAdd(MakeDate(2007, 2, 10), 365, "WD") &rarr; Monday, 2008-July-7</code>
  
 
This generates a series of dates with the 1st and 15th of each month:
 
This generates a series of dates with the 1st and 15th of each month:
var J := 0..24;
+
:<code>Var J := 0..24;</code>
DateAdd(MakeDate(2006, 1, IF Mod(J, 2) = 0 Then 1 Else 15), Floor(J/2), "M")
+
:<code>DateAdd(MakeDate(2006, 1, IF Mod(J, 2) = 0 Then 1 Else 15), Floor(J/2), "M") &rarr;</code>
&rarr;
+
:<code>[2006-Jan-1, 2006-Jan-15, 2006-Feb-1, 2006-Feb-15, 2006-Mar-1, ..., 2007-Jan-1]</code>
[2006-Jan-1, 2006-Jan-15, 2006-Feb-1, 2006-Feb-15, 2006-Mar-1, ..., 2007-Jan-1]
+
  
 
===Today()===
 
===Today()===
 
 
Returns the date number (number of days since base date, usually Jan 1, 1904, as in Excel) for the day on which the function is evaluated. Unlike other functions, it gives a different value every day the model is run.
 
Returns the date number (number of days since base date, usually Jan 1, 1904, as in Excel) for the day on which the function is evaluated. Unlike other functions, it gives a different value every day the model is run.
  
;Time(withTime, utc)
+
;Syntax: [[Today]](withTime, utc)
 
+
Today() accepts two optional flags:  Setting ''WithTime'' to true returns the current time of day in the fractional part of the result, by default for your local time zone. If you set ''utc'' to true, it returns the date and time in Universal Time Coordinated (UTC), a.k.a. Greenwich Mean Time.
+
 
+
Note that the result is the date and time when the function is evaluated in this session.  The result is cached in the value of a variable that uses Today(). It will not be updated when the date or time changes unless you trigger a re-evaluation -- e.g. by reloading the model or by changing another input to the variable that uses Today().
+
  
;Library: [[Special functions]]
+
[[Today]]() accepts two optional flags:  Setting «WithTime» to <code>True</code> returns the current time of day in the fractional part of the result, by default for your local time zone. If you set «utc» to true, it returns the date and time in Universal Time Coordinated (UTC), a.k.a. Greenwich Mean Time.
  
 +
Note that the result is the date and time when the function is evaluated in this session.  The result is cached in the value of a variable that uses [[Today]](). It will not be updated when the date or time changes unless you trigger a re-evaluation -- e.g. by reloading the model or by changing another input to the variable that uses [[Today]]().
  
;Library: [[Special functions]]
+
;Library: [[Special_Functions_library|Special functions]]
  
See also: [[Special Handling of Date Values]]
+
==See also==
 +
* [[Date functions]]
 +
* [[Date formats]]
 +
* [[Date and Time Values]]
 +
* [[Special Handling of Date Values]]
 +
* [[MakeDate]]
 +
* [[MakeTime]]
 +
* [[DatePart]]
 +
* [[DateAdd]]
 +
* [[Today]]
 +
* [[YearFrac]]
 +
* [[ParseDate]]
 +
* [[Special Functions library]]

Latest revision as of 23:46, 7 October 2021




Release:

4.6  •  5.0  •  5.1  •  5.2  •  5.3  •  5.4  •  6.0


Analytica represents a date as the number of days since Jan 1, 1904, (or since Jan 1, 1900, if you check use Excel data origin in Preferences) . You can enter or display a date in almost any format -- using US conventions or ROW (Rest Of World) using Regional and Language settings. A time is expressed as a fraction of a day. You can set the display format for a number as a date or time in the Number format dialog. You can create a date or time with MakeDate() and MakeTime(), get the current date or time with Today(), select an element of a date with DatePart(). DateAdd() lets you add a hour, day, month, or other interval to a date. It is also useful for creating an Index of dates or times.

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
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. Some examples:
dd-MM-yy -- e.g., 05-8-06
'Q'Q YYYY -- e.g., Q2 2006
www, d MMM yyyy -- e.g., Thu, 5 Aug 2006
wwww, d of MMMM, yyyy -- e.g., Thursday, 5 of August, 2006
d-MMM-yyyy hh:mm:ss tt -- e.g., 5 Aug 2006 05:45:00 PM

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

d: numeric 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 set a date format for an input variable or Edit table, you can enter dates in any acceptable date format -- no matter what date format was specified. For example, a variable with a date format interprets "9/11/2001" as "11 September, 2001" on a computer set to the 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, click 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. The default date origin is Jan 1, 1904 -- as used by most Macintosh applications, including Excel on Macintosh. To can change the date origin to Jan 1, 1900 -- the default in Excel on Windows and most other Windows software -- open the 'Preferences dialog and check Use Excel date origin.

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 Excel incorrectly treats Feb 29, 1900 as 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, valueForInvalid)

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

new to Analytica 5.0 The optional «valueForInvalid» specifies a value to return when there is no date having the given «year», «month» and «day». When this parameter is omitted, it returns the closest valid date in the same month when possible.

Examples
MakeDate(2007, 5, 15) → 15-May-2007
MakeDate(2000) → 1-Jan-2000
MakeDate(2015, 2, 29, valueForInvalid: null) → Null
MakeDate(2015, 2, 29) → 28-Feb-2015
Requirements
«year», «month», and «date» should be positive integers, or coercible to positive numbers.
Library
Special functions

MakeTime(h, m, s)

Gives the time of day as a fraction of a day, 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.

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

DatePart(date, part)

Gives the year, month, day, or other part of a date value «date» as a number. 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).

Example
DatePart(MakeDate(2006, 2, 28), 'D') → 28
Expects
«date» as a date number, and «part» as 'Y', 'M', or 'D' (upper- or lowercase, by 'M' must be upper).
Syntax
DatePart(date: Numeric; part: Text)
Library
Special functions
Less Common Options
«part» may also be:
  • 'MMM' (returns e.g., 'Jan'),
  • 'MMMM' (e.g., 'January'),
  • 'EEE' (in English, e.g., 'Jan'),
  • 'EEEE' (in English, 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).
  • 'date' (the date part, dropping the time part.)
Time
  • '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"),
  • 'tt' ("AM" or "PM").
  • 'time' (the time part only, dropping the date part)
Elapsed days
  • '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 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.

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

DateAdd(date, offset, unit)

Gives a date value that is «offset» from «date» by «unit» "Y" (years), "Q" (quarters) "M" (months) "D" (days), <coed>"WD"</code> (weekdays, skipping over Saturdays and Sundays. It also works for time increments: "h" (hours), "m" (minutes) or "s" (seconds).

Examples

DateAdd() is especially useful for generating a sequence 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 display format for the dates depends on what was set in Number format.

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, it returns the last day of the month.

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.

Expects
«date» and «offset» to be numbers, and «unit» to be 'Y', 'Q', 'M', 'D', 'WD', 'h', 'm', or 's'. The unit is not case sensitive except for 'M' (months) and 'm' (minutes).
Syntax
DateAdd(date, offset: Number; unit: Text)
Library
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

This generates a series of dates with the 1st and 15th 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]

Today()

Returns the date number (number of days since base date, usually Jan 1, 1904, as in Excel) for the day on which the function is evaluated. Unlike other functions, it gives a different value every day the model is run.

Syntax
Today(withTime, utc)

Today() accepts two optional flags: Setting «WithTime» to True returns the current time of day in the fractional part of the result, by default for your local time zone. If you set «utc» to true, it returns the date and time in Universal Time Coordinated (UTC), a.k.a. Greenwich Mean Time.

Note that the result is the date and time when the function is evaluated in this session. The result is cached in the value of a variable that uses Today(). It will not be updated when the date or time changes unless you trigger a re-evaluation -- e.g. by reloading the model or by changing another input to the variable that uses Today().

Library
Special functions

See also

Comments


You are not allowed to post comments.