Date and Time Functions

The functions that relate to date-time values and time-span values are:

 

DATE(year,month,day)

This function returns the DateTime object for a particular date, specified by the year, month, and day.

example: DATE(2004,2,10) gives the result February 10, 2004

 

DATEDIF(date1,date2,outputcode)

This function returns the number of days, months, or years between two dates.

code

description

"D"

The number of days between date1 and date2

"M"

The number of complete months between date1 and date2

"Y"

The number of complete years between date1 and date2

"YD"

The number of days between date1 and date2 as if they were in the same year

"YM"

The number of months between date1 and date2 as if they were in the same year

"MD"

The number of days between date1 and date2 as if they were in the same month and year

example: DATEDIF("2001/1/1","2003/1/1","Y") returns 2

 

DATEVALUE(date_string)

This function returns a DateTime object of the specified date.

example: DATEVALUE("2004/10/6") gives the result 10/6/2004 12:00:00 AM

 

DAY(date)

This function returns the day number of the month (integer 1 to 31) that corresponds to the specified date.

example: DAY("7/4/2003 12:00") gives 7

 

DAYS360(start_date,end_date,method)

This function returns the number of days between two dates based on a 360-day year.

method

description

startdate

Date from which to calculate days

enddate

Date to which to calculate days

method

[Optional] Method for calculating days; if FALSE or omitted, uses U.S. (NASD) method; if TRUE, uses European method.

example: DAYS360("7/15/2004","12/25/2004") gives the result 160

 

EDATE(start,months)

This function calculates the date that is the indicated number of months before or after a specified date.

argument

description

startdate

Starting date

months

Number of months before (negative) or after (positive) the starting date; if not an integer, the number is truncated

example: EDATE("2004/01/09",2) gives the result 3/9/2004 12:00:00 AM

 

 

EOMONTH(start,months)

This function calculates the date for the last day of the month (end of month) that is the indicated number of months before or after the starting date.

argument

description

startdate

Starting date

months

Number of months before (negative) or after (positive) the starting date; if not an integer, the number is truncated

example: EOMONTH("2004/01/09",2) gives the result 3/31/2004 12:00:00 AM

 

 

HOUR(time)

This function returns the hour that corresponds to a specified time.

example: HOUR("2:22 PM") gives the result 14

 

MINUTE(time)

This function returns the minute corresponding to a specified time.

example: MINUTE("12:17") gives the result 17

 

MONTH(date)

This function returns the month corresponding to the specified date value.

example: MONTH("12/17/2004") gives the result 12

 

NETWORKDAYS(startdate,enddate,holidays)

This function returns the total number of complete working days between the start and end dates.

argument

description

startdate

Date that is the starting date; a number (as in 37806.5), or a DateTime object, as in DATE(2003,7,4)

enddate

Date that is the ending date; a number (as in 37806.5), or a DateTime object, as in DATE(2003,7,4)

holidays

[Optional] Range of dates to exclude from the calculation; if omitted, the calculation assumes no holidays and all weekdays are workdays

 

NOW()

This function returns the current date and time.

 

SECOND(time)

This function returns the seconds (0 to 59) value for a specified time.

example: SECOND(0.01) gives the result 24

 

TIME(hour,minutes,seconds)

This function returns the TimeSpan object for a specified time.

argument

description

hour

Hour as a number from 0 to 23.

minutes

Minutes as a number from 0 to 59.

seconds

Seconds as a number from 0 to 59.

example: TIME(16,48,10) gives the result 16:48:10

 

TIMEVALUE(time_string)

This function returns the TimeSpan object of the time represented by a text string.

Example: TIMEVALUE("17:29") gives the result 17:29

 

TODAY()

This function returns the date and time of the current date.

 

WEEKDAY(date,type)

This function returns the number corresponding to the day of the week for a specified date.

argument

description

date

Date for which you want to determine the day of the week provided

type

[Optional] Number that represents the numbering scheme for the returned weekday value; can be any of:

 

Value

Number returned

 

1 or omitted

Numbers 1 (Sunday) through 7 (Saturday)

 

2

Numbers 1 (Monday) through 7 (Sunday)

 

3

Numbers 0 (Monday) through 6 (Sunday)

example: WEEKDAY(46,2) gives the result 3

 

 

WEEKNUM(date,weektype)

This function returns a number that indicates the week of the year numerically.

argument

description

date

Date for which you want to determine the number of week

weektype

Type of week determined by on which day the week starts

 

Value

Number returned

 

1 (assumed if omitted)

Week starts on a Sunday

 

2

Week starts on a Monday

example: WEEKNUM(23,1) gives the result 4

 

WORKDAY(startdate,numdays,holidays)

This function returns the number of working days before or after the starting date.

argument

description

startdate

Date that is the starting date; a number (as in 37806.5), or a DateTime object, as in DATE(2003,7,4)

numdays

Number of non-weekend or non-holiday days before or after the starting date; days in the future are positive and days in the past are negative; if not an integer, the number is truncated

holidays

[Optional] Range of dates to exclude from the calculation; if omitted, the calculation assumes no holidays and all weekdays are workdays

 

 

YEAR(date)

This function returns the year as an integer for a specified date.

example: YEAR("8/9/2004") gives the result 2004

 

YEARFRAC(startdate,enddate,basis)

This function returns the fraction of the year represented by the number of whole days between the start and end dates.

argument

description

startdate

Starting date (DateTime object)

enddate

Ending date (DateTime object)

basis

[Optional] Integer representing the basis for day count (Refer to Day Count Basis.)

 

 

 

See Also:


Formula Functions