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:

DatePart(serial_number, "H")

MINUTE(serial_number)

Analytica equivalent:

DatePart(serial_number, "m")

MONTH(serial_number)

Analytica equivalent:

DatePart(serial_number, "M")

NETWORKDAYS(start_date, end_date)

Analytica equivalent:

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

NOW

Analytica equivalent:

Today(withTime:true)

SECOND

Analytica equivalent:

DatePart(serial_number, "s")

TIME(hour, minute, second)

Analytica equivalent:

MakeTime(hour, minute, second)

TIMEVALUE(time_text)

This number parses a time of day string, such as "1:30:45 PM", returning the Excel serial number for the time (a number between 0 and 1).

A common way of accomplishing this in Analytica is to create a variable node that will hold the time value, set its number format to "Date", and then enter the time as the definition, without quotes. The result of that variable is the same as the result of Excel's TIMEVALUE. In the number format, you can select a custom time format to actually view the number as a time, although the underlying value is a number between 0 and 1.

A second method is to use MakeTime(h, m, s) -- for example:

MakeTime(13, 30, 45) → 0.563020833

You can introduce this function as a User-Defined Function by creating two UDFs:

Function ParseNum(x: coerce number) := x

Function TimeValue(s: text) := ParseNum(s)

And then set TimeValue's number format to Date before using. The same function will also parse a date or a date-time.

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)

Analytica equivalent:

YearFrac(start_date, end_date, basis)

This excel function returns the fraction of a year between two dates. The basis parameter selects different denominators -- e,g, 365 or 360:

(end_date - start_date)/365

See Also

• Excel to Analytica Mappings