Date Functions

• CDate
• Date
• DateAdd
• DateDiff
• DatePart
• DateSerial
• DateValue
• Hour
• ISO8601ToDate
• IsDate
• LastDayInMonth
• LocalToUTCTime
• Minute
• Month
• MonthName
• Now
• NumberToDate
• Second
• Time
• Timer
• TimeSerial
• TimeValue
• UTCToLocalTime
• Weekday
• WeekdayName
• Year

CDate

Description

Converts a value to a date.

Syntax

CDate(expression)

Arguments

• expression
  • Must be a valid date.

Example

' Parsed using the IMan server's locale, so "12/03/2026"
' is 12 March on a UK server and 3 December on a US one.
' Test IsDate first when the source cannot be trusted.
CDate("12/03/2026")   ' Returns 12/03/2026 on a UK server

Date

Description

Returns the current system date.

Syntax

Date

Arguments

• None

Example

' Today's date on the IMan server, with no time
' component.
Date()   ' Returns e.g. 12/03/2026

DateAdd

Description

Returns a date after which a certain time/date interval has been added.

Syntax

DateAdd (interval, number, date)

Arguments

• interval
  • The time/date interval that you wish to add. It can be one of the following values:

Interval	Explanation
yyyy	Year
Q	Quarter
m	Month
Y	Day of Year
D	Day
w	Weekday
ww	Week
H	Hour
N	Minute
S	Second

• number
  • The number of intervals that you wish to add.
• date
  • The date to which the interval should be added.

Example

' Add an interval. Negative values subtract.
DateAdd("d", 30, %OrdDate)    ' 12/03/2026 returns 11/04/2026
DateAdd("m", 1, %OrdDate)     ' Returns 12/04/2026
DateAdd("d", -1, %OrdDate)    ' Returns 11/03/2026

DateDiff

Description

Returns the difference between two date values, based on the interval specified.

Syntax

DateDiff( interval, date1, date2, [firstdayofweek], [firstweekofyear] )

Arguments

• interval
  • The interval of time to use to calculate the difference between date1 and date2.
  • Below is a list of valid interval values:

Interval	Explanation
yyyy	Year
q	Quarter
m	Month
y	Day of Year
d	Day
w	Weekday
ww	Week
h	Hour
n	Minute
s	Second

• date1 and date2
  • The two dates to calculate the difference between.
• firstdayofweek
  • Optional. It is a constant that specifies the first day of the week. If this parameter is omitted, Excel assumes that Sunday is the first day of the week.
• firstweekofyear
  • Optional. It is a constant that specifies the first week of the year. If this parameter is omitted, Excel assumes that the week containing Jan 1^st is the first week of the year.

Example

' Whole intervals between two dates, earlier date first.
DateDiff("d", %OrdDate, CDate("30/04/2026"))   ' Returns 49

DatePart

Description

Returns a specified part of a given date.

Syntax

DatePart( interval, date, [firstdayofweek], [firstweekofyear] )

Arguments

• interval
  • The interval of time that you wish to return. This parameter can be any one of the following valid interval values:

Interval	Explanation
yyyy	Year
Q	Quarter
m	Month
Y	Day of Year
D	Day
w	Weekday
ww	Week
H	Hour
N	Minute
S	Second

• date
  • The date value that you wish to evaluate.
• firstdayofweek
  • Optional. It is a constant that specifies the first day of the week. If this parameter is omitted, Excel assumes that Sunday is the first day of the week. This parameter can be one of the following values:

Value	Explanation
0	Use the NLS API setting
1	Sunday (default)
2	Monday
3	Tuesday
4	Wednesday
5	Thursday
6	Friday
7	Saturday

• firstweekofyear
  • Optional. It is a constant that specifies the first week of the year. If this parameter is omitted, Excel assumes that the week containing Jan 1^st is the first week of the year. This parameter can be one of the following values:

Value	Explanation
0	Use the NLS API setting
1	Use the first week that includes Jan 1st (default)
2	Use the first week in the year that has at least 4 days
3	Use the first full week of the year

Example

' One component of a date. "q" is the quarter.
DatePart("q", %OrdDate)    ' 12/03/2026 returns 1
DatePart("yyyy", %OrdDate) ' Returns 2026

DateSerial

Description

Returns a date given a year, month, and day value.

Syntax

DateSerial( year, month, day )

Arguments

• year
  • A numeric value between 100 and 9999 that represents the year value of the date.
• month
  • A numeric value that represents the month value of the date.
• day
  • A numeric value that represents the day value of the date.

Example

' Build a date from year, month and day. Out-of-range
' parts roll over, which is what makes day 0 useful: it
' is the last day of the month before, so this returns
' 31/03/2026 rather than erroring.
DateSerial(2026, 3, 12)   ' Returns 12/03/2026
DateSerial(2026, 4, 0)    ' Returns 31/03/2026

DateValue

Description

returns the serial number of a date

Syntax

DateValue( date )

Arguments

• date
  • A string representation of a date.

Example

' Parses text to a date and discards any time component.
DateValue("12 March 2026")   ' Returns 12/03/2026

Day

Description

Returns the day of the month (a number from 1 to 31) given a date value.

Syntax

Day( date )

Arguments

• date
  • Must be a valid date.

Example

Day(%OrdDate)   ' 12/03/2026 returns 12

Hour

Description

Returns the hour of a time value (from 0 to 23).

Syntax

Hour( time )

Arguments

• time
  • The time value to extract the hour from. It may be expressed as a string value, a decimal number, or the result of a formula.

Example

' 24-hour clock.
Hour(%OrdDate)   ' 12/03/2026 09:35:00 returns 9

ISO8601ToDate

Description

Converts a string formatted as an ISO8601 date/time to a date/time value.

Syntax

ISO8601ToDate ( isodate )

Arguments

• isodate
  • A string in the format of:
    • Complete Date
      • YYYY-MM-DD (eg 1997-07-16);
    • Complete Date plus hours and minutes
      • YYYY-MM-DDThh:mmTZD (eg 1997-07-16T19:20+01:00)
    • Complete Date plus hours, minutes and seconds
      • YYYY-MM-DDThhssTZD (eg 1997-07-16T19:20:30+01:00)
    • Complete Date plus hours, minutes, seconds and a decimal fraction of a second
      • YYYY-MM-DDThhss.sTZD (eg 1997-07-16T19:20:30.45+01:00);
  • Where:

YYYY	four-digit year
MM	two-digit month (01=January, etc.)
DD	two-digit day of month (01 through 31)
hh	two digits of hour (00 through 23) (am/pm OT allowed)
mm	two digits of minute (00 through 59)
ss	two digits of second (00 through 59)
s	one or more digits representing a decimal fraction of a second
TZD	Time zone designator (Z or +hh:mm or -hh:mm)

Example

' IMan-specific, and the one to be careful with. It
' accepts anything the .NET date parser accepts -- not
' strictly ISO 8601 -- and always converts the result to
' the IMan SERVER's local time.
'
' So a value carrying an offset or a trailing Z is
' shifted into server time, and a value carrying NO
' timezone is treated as UTC and shifted anyway. On a
' UTC server both are no-ops; elsewhere they are not.
ISO8601ToDate("2026-03-12T09:35:00Z")        ' 09:35 UTC in server time
ISO8601ToDate("2026-03-12T09:35:00+02:00")   ' 07:35 UTC in server time

IsDate

Description

Returns true if the expression is a valid date. Otherwise, it returns false.

Syntax

IsDate( expression )

Arguments

• expression
  • A variant.

Example

' Guard a cast against text that will not parse.
If IsDate(%OrdDate) Then
  CDate(%OrdDate)
Else
  Null
End If

LastDayInMonth

Description

Returns the day of the month of the last day in the month — that is, the number of days the month has. The return value is an integer between 28 and 31.

Syntax

LastDayInMonth( month, year )

Arguments

• month
  • An integer between 1 and 12 representing the month.
• year
  • A valid year.

Example

' IMan-specific. Note the arguments: the MONTH first and
' the YEAR second, not a date. It returns a day number,
' so it is really a month-length function.
LastDayInMonth(3, 2026)   ' Returns 31
LastDayInMonth(2, 2026)   ' Returns 28 -- 2026 is not a leap year

' To get the date itself, build it:
DateSerial(Year(%OrdDate), Month(%OrdDate), LastDayInMonth(Month(%OrdDate), Year(%OrdDate)))
' 12/03/2026 returns 31/03/2026

LocalToUTCTime

Description

Converts a local time to a UTC Time.

Syntax

LocalToUTCTime( date )

Arguments

• date
  • The local date/time to be converted to a UTC date/time.

Example

' IMan-specific. Converts from the IMan SERVER's
' timezone to UTC -- the server's, not the customer's
' and not the connector's. Use it when a webservice
' expects UTC timestamps.
LocalToUTCTime(%OrdDate)   ' 09:35 on a UTC+1 server returns 08:35

Minute

Description

Returns the minute of a time value (from 0 to 59).

Syntax

Minute( time )

Arguments

• time
  • The time value to extract the minute from. It may be expressed as a string value, a decimal number, or the result of a formula.

Example

Minute(%OrdDate)   ' 12/03/2026 09:35:00 returns 35

Month

Description

Returns the month (a number from 1 to 12) given a date value.

Syntax

Month( date_value )

Arguments

• date_value
  • A valid date.

Example

Month(%OrdDate)   ' 12/03/2026 returns 3

MonthName

Description

Returns a string representing the month given a number from 1 to 12.

Syntax

MonthName( number, [ abbreviate ] )

Arguments

• number
  • A value from 1 to 12, representing the month.
• abbreviate
  • Optional. This parameter accepts a boolean value, either TRUE or FALSE.
  • If this parameter is set to TRUE, it means that the month name is abbreviated.
  • If this parameter is set to FALSE, the month name is not abbreviated.

Example

' Pass True as the second argument to abbreviate.
MonthName(Month(%OrdDate))         ' Returns "March"
MonthName(Month(%OrdDate), True)   ' Returns "Mar"

Now

Description

Returns the current system date and time.

Syntax

Now

Arguments

• None

Example

' Date and time on the IMan server.
Now()   ' Returns e.g. 12/03/2026 09:35:00

NumberToDate

Description

Returns a date from date represented in number in the format yyyymmdd.

Syntax

NumberToDate( number )

Arguments

• number
  • An integer in the format yyyymmdd

Example

' IMan-specific. The number must be yyyyMMdd and exactly
' eight digits -- it is NOT an OLE serial date or a Unix
' timestamp. Anything else raises an out-of-range error,
' except null or 0, which return 01/01/1900.
NumberToDate(20260312)   ' Returns 12/03/2026
NumberToDate(0)          ' Returns 01/01/1900

Second

Description

Returns a whole number between 0 and 59, inclusive, representing the second of the minute.

Syntax

Second ( time )

Arguments

• time
  • Any expression that can represent a time. If time contains Null, Null is returned.

Example

Second(%OrdDate)   ' 12/03/2026 09:35:00 returns 0

Time

Description

Returns the current system time.

Syntax

Time

Arguments

• None

Example

' The time of day on the IMan server, with no date
' component.
Time()   ' Returns e.g. 09:35:00

Timer

Description

Returns the number of seconds from midnight.

Syntax

Timer

Arguments

• None

Example

' Seconds elapsed since midnight, to a fraction. For
' timing a step, not for stamping a record.
Timer()   ' Returns e.g. 34500.13

TimeSerial

Description

Returns a time given an hour, minute, and second value.

Syntax

TimeSerial( hour, minute, second )

Arguments

• hour
  • A numeric value between 0 and 23 that represents the hour value of the time.
• minute
  • A numeric value that represents the minute value of the time.
• second
  • A numeric value that represents the second value of the time.

Example

TimeSerial(9, 35, 0)   ' Returns 09:35:00

TimeValue

Description

Returns the serial number of a time.

Syntax

TimeValue( time_value )

Arguments

• time_value
  • A string representation of a time.

Example

' Parses text to a time and discards any date component.
TimeValue("09:35:00")   ' Returns 09:35:00

UTCToLocalTime

Description

Converts a UTC date/time to a local date/time.

Syntax

UTCToLocalTime( date )

Arguments

• date
  • The UTC date/time to be converted to a local date/time.

Example

' IMan-specific. Converts a UTC value into the IMan
' SERVER's timezone -- the server's, not the customer's.
' Use it on timestamps arriving from a webservice that
' returns UTC.
UTCToLocalTime(%OrdDate)   ' 08:35 UTC on a UTC+1 server returns 09:35

Weekday

Description

Returns a number representing the day of the week, given a date value.

Syntax

Weekday( date, firstdayofweek )

Arguments

• date
  • Any expression that can represent a date. If date contains Null, Null is returned. date expressed as a serial number or a date in quotation marks.
• firstdayofweek
  • A constant that specifies the first day of the week. If omitted, vbSunday is assumed.

Value	Explanation
0	Use the NLS API setting
1	Sunday (default)
2	Monday
3	Tuesday
4	Wednesday
5	Thursday
6	Friday
7	Saturday

Example

' 1 to 7, Sunday being 1 by default. Pass 2 as the
' second argument to start the week on Monday.
Weekday(%OrdDate)      ' 12/03/2026 is a Thursday, so returns 5
Weekday(%OrdDate, 2)   ' Week starting Monday, so returns 4

WeekdayName

Description

Returns a string representing the day of the week given a number from 1 to 7.

Syntax

WeekdayName( number, [ abbreviate], [ firstdayofweek ] )

Arguments

• number
  • A value from 1 to 7, representing a day of the week.
• abbreviate
  • Optional. This parameter accepts a boolean value, either TRUE or FALSE. If this parameter is set to TRUE, it means that the weekday name is abbreviated. If this parameter is set to FALSE, the weekday name is not abbreviated.
• firstdayofweek
  • Optional. It determines what day is to be the first day of the week. It can be any of the following values:

Value	Explanation
0	Use the NLS API setting
1	Sunday (default)
2	Monday
3	Tuesday
4	Wednesday
5	Thursday
6	Friday
7	Saturday

If this parameter is omitted, the Weekday function assumes that the first day of the week is Sunday.

Example

' Pass True as the second argument to abbreviate.
WeekdayName(Weekday(%OrdDate))         ' Returns "Thursday"
WeekdayName(Weekday(%OrdDate), True)   ' Returns "Thu"

Year

Description

Returns a four-digit year (a number from 1900 to 9999) given a date value.

Syntax

Year( date_value )

Arguments

• date_value
  • A valid date.

Example

Year(%OrdDate)   ' 12/03/2026 returns 2026