Date Functions

Back to top

CDate

Description

Converts a value to a date.

Syntax

  • CDate(expression)

Arguments

  • expression
    • Must be a valid date.
Back to top

Date

Description

Returns the current system date.

Syntax

  • Date

Arguments

  • None
Back to top

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.
Back to top

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

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 1st 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
Back to top

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.
Back to top

DateValue

Description

returns the serial number of a date

Syntax

  • DateValue( date )

Arguments

  • date
    • A string representation of a date.
Back to top

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.
Back to top

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.
Back to top

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:

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)
Back to top

IsDate

Description

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

Syntax

  • IsDate( expression )

Arguments

  • expression
    • A variant.
Back to top

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.
Back to top

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.
Back to top

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.
Back to top

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.
Back to top

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.
Back to top

Now

Description

Returns the current system date and time.

Syntax

  • Now

Arguments

  • None
Back to top

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
Back to top

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.
Back to top

Time

Description

Returns the current system time.

Syntax

  • Time

Arguments

  • None
Back to top

Timer

Description

Returns the number of seconds from midnight.

Syntax

  • Timer

Arguments

  • None
Back to top

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.
Back to top

TimeValue

Description

Returns the serial number of a time.

Syntax

  • TimeValue( time_value )

Arguments

  • time_value
    • A string representation of a time.
Back to top

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.
Back to top

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
   
Back to top

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.

Back to top

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.