Date Functions

CDate

Description

Converts a value to a date.

Syntax

• CDate(expression)

Arguments

• expression
  • Must be a valid date.

Date

Description

Returns the current system date.

Syntax

• Date

Arguments

• None

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:

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

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:

• 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 1st is the first week of the year.

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:

• 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:

• 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 1st is the first week of the year. This parameter can be one of the following values:

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.

DateValue

Description

returns the serial number of a date

Syntax

• DateValue( date )

Arguments

• date
  • A string representation of a date.

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.

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.

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-DDThh:mm:ssTZD (eg 1997-07-16T19:20:30+01:00)
    • Complete Date plus hours, minutes, seconds and a decimal fraction of a second
      • YYYY-MM-DDThh:mm:ss.sTZD (eg 1997-07-16T19:20:30.45+01:00);
  • Where:

IsDate

Description

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

Syntax

• IsDate( expression )

Arguments

• expression
  • A variant.

LastDayInMonth

Description

Returns the weekday of the last day in the month. The return value is the integer value of the weekday.

Syntax

• LastDayInMonth( month, year )

Arguments

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

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.

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.

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.

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.

Now

Description

Returns the current system date and time.

Syntax

• Now

Arguments

• None

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

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.

Time

Description

Returns the current system time.

Syntax

• Time

Arguments

• None

Timer

Description

Returns the number of seconds from midnight.

Syntax

• Timer

Arguments

• None

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.

TimeValue

Description

Returns the serial number of a time.

Syntax

• TimeValue( time_value )

Arguments

• time_value
  • A string representation of a time.

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.

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.

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:

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

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.