Difference between revisions of "Excel to Analytica Mappings/Date and Time Functions"
(→DATE) |
|||
| Line 4: | Line 4: | ||
Analytica equivalent: | Analytica equivalent: | ||
| − | MakeDate(year,month,day) | + | [[MakeDate]](year,month,day) |
| + | |||
| + | = DATEVALUE(date_text) = | ||
| + | |||
| + | Analytica equivalent: | ||
| + | [[ParseDate]](date_text) | ||
| + | |||
| + | Note: [[ParseDate]] requires the Flat File Library to be included in your model. | ||
| + | |||
| + | = DAY(serial_number) = | ||
| + | |||
| + | Analytica equivalent: | ||
| + | [[DatePart]](serial_number,"D") | ||
| − | |||
| − | |||
= DAYS360 = | = DAYS360 = | ||
| − | = EDATE = | + | |
| − | = EOMONTH = | + | = EDATE(start_date,months) = |
| − | = HOUR = | + | |
| − | = MINUTE = | + | Analytica equivalent: |
| − | = MONTH = | + | [[DateAdd]](start_date,months,"M") |
| − | = NETWORKDAYS = | + | |
| + | = EOMONTH(start_date,months) = | ||
| + | |||
| + | Analytica equivalent: | ||
| + | Var d := [[DateAdd]](start_date,months,"M"); | ||
| + | [[MakeDate]]([[DatePart]](d,"Y"),[[DatePart]](d,"M"),31) | ||
| + | |||
| + | = HOUR(serial_number) = | ||
| + | |||
| + | Analytica equivalent (release 4.0.1 or later): | ||
| + | [[DatePart]](serial_number,"H") | ||
| + | |||
| + | = MINUTE(serial_number) = | ||
| + | |||
| + | Analytica equivalent (release 4.0.1 or later): | ||
| + | [[DatePart]](serial_number,"m") | ||
| + | |||
| + | = MONTH(serial_number) = | ||
| + | |||
| + | Analytica equivalent: | ||
| + | [[DatePart]](serial_number,"M") | ||
| + | |||
| + | = NETWORKDAYS(start_date,end_date) = | ||
| + | |||
= NOW = | = NOW = | ||
| + | |||
| + | Analytica equivalent: | ||
| + | [[Today]]( withTime:true ) | ||
| + | |||
| + | The ''withTime'' parameter requires Analytica 4.0.1 or later. | ||
| + | |||
= SECOND = | = SECOND = | ||
| + | |||
| + | Analytica equivalent (release 4.0.1 or later): | ||
| + | [[DatePart]](serial_number,"s") | ||
| + | |||
= TIME = | = TIME = | ||
| + | |||
= TIMEVALUE = | = TIMEVALUE = | ||
| − | = TODAY = | + | |
| − | = WEEKDAY = | + | = TODAY() = |
| − | = WORKDAY = | + | |
| − | = YEAR = | + | Analytica equivalent: |
| − | = YEARFRAC = | + | Today() |
| + | |||
| + | = WEEKDAY(serial_number) = | ||
| + | |||
| + | Analytica equivalent: | ||
| + | DatePart(serial_number,"w") | ||
| + | |||
| + | Excel also allows an optional parameter to alter the numbering. These become: | ||
| + | WEEKDAY(serial_number,1) → DatePart(serial_number,"w") | ||
| + | WEEKDAY(serial_number,2) → DatePart(serial_number-1,"w") | ||
| + | WEEKDAY(serial_number,3) → DatePart(serial_number-1,"w")-1 | ||
| + | |||
| + | |||
| + | = WORKDAY(start_date,end_date) = | ||
| + | |||
| + | = YEAR(serial_number) = | ||
| + | |||
| + | Analytica equivalent: | ||
| + | DatePart(serial_number,"Y") | ||
| + | |||
| + | = YEARFRAC(start_date,end_date,basis) = | ||
| + | |||
| + | This excel function returns the fraction of a year represented by the two dates. The basis parameter selects different denominators. There is current no exact equivalent for this in Analytica, but you would usually use something like: | ||
| + | |||
| + | (end_date - start_date) / 365 | ||
| + | |||
| + | where you adjust the denominator to match your choice of basis. | ||
Revision as of 00:34, 11 January 2008
This page shows how selected Excel Date and Time Functions map to Analytica equivalents.
DATE(year,month,day)
Analytica equivalent:
MakeDate(year,month,day)
DATEVALUE(date_text)
Analytica equivalent:
ParseDate(date_text)
Note: ParseDate requires the Flat File Library to be included in your model.
DAY(serial_number)
Analytica equivalent:
DatePart(serial_number,"D")
DAYS360
EDATE(start_date,months)
Analytica equivalent:
DateAdd(start_date,months,"M")
EOMONTH(start_date,months)
Analytica equivalent:
Var d := DateAdd(start_date,months,"M"); MakeDate(DatePart(d,"Y"),DatePart(d,"M"),31)
HOUR(serial_number)
Analytica equivalent (release 4.0.1 or later):
DatePart(serial_number,"H")
MINUTE(serial_number)
Analytica equivalent (release 4.0.1 or later):
DatePart(serial_number,"m")
MONTH(serial_number)
Analytica equivalent:
DatePart(serial_number,"M")
NETWORKDAYS(start_date,end_date)
NOW
Analytica equivalent:
Today( withTime:true )
The withTime parameter requires Analytica 4.0.1 or later.
SECOND
Analytica equivalent (release 4.0.1 or later):
DatePart(serial_number,"s")
TIME
TIMEVALUE
TODAY()
Analytica equivalent:
Today()
WEEKDAY(serial_number)
Analytica equivalent:
DatePart(serial_number,"w")
Excel also allows an optional parameter to alter the numbering. These become:
WEEKDAY(serial_number,1) → DatePart(serial_number,"w") WEEKDAY(serial_number,2) → DatePart(serial_number-1,"w") WEEKDAY(serial_number,3) → DatePart(serial_number-1,"w")-1
WORKDAY(start_date,end_date)
YEAR(serial_number)
Analytica equivalent:
DatePart(serial_number,"Y")
YEARFRAC(start_date,end_date,basis)
This excel function returns the fraction of a year represented by the two dates. The basis parameter selects different denominators. There is current no exact equivalent for this in Analytica, but you would usually use something like:
(end_date - start_date) / 365
where you adjust the denominator to match your choice of basis.
Enable comment auto-refresher