Excel to Analytica Mappings/Date and Time Functions

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")

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)

Analytica equivalent (requires 4.1):

DatePart(end_date,"wd+") - DatePart(start_date,"wd-")

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(hour,minute,second)

Analytica equivalent (requires 4.0.1 or later):

MakeTime(hour,minute,second)

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)

Analytica equivalent:

DatePart(end_date,"wd+") - DatePart(start_date,"wd-")

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.