[Expand]General Information
[Expand]WinForms Controls
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Collapse]WPF Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Common Concepts
 [Expand]MVVM Framework
 [Collapse]Controls and Libraries
   Reporting
  [Expand]Data Grid
  [Expand]Ribbon, Bars and Menu
  [Expand]Charts Suite
  [Expand]Pivot Grid
  [Expand]Scheduler
  [Collapse]Spreadsheet
    Product Structure
   [Expand]Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Collapse]Formulas
    [Collapse]Functions
      Mathematical Functions
      Statistical Functions
      Date and Time Functions
      Text Functions
      Financial Functions
      Logical Functions
      Lookup and Reference Functions
      Engineering Functions
      Information Functions
      Compatibility Functions
      Database Functions
      Web Functions
      User-Defined Functions (UDF)
      Real Time Data (RTD) function
     Operators
     Array Formulas
     Calculation
     Formula Engine
     Formula AutoComplete
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Shapes
    Printing
    Protection
    Find and Replace
   [Expand]Mail Merge
   [Expand]Visual Elements
    Services
    Themes and Templates
   [Expand]Shortcuts
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Tree List
  [Expand]Gauge Controls
  [Expand]Map Control
  [Expand]Layout Management
  [Expand]Windows Modern UI
  [Expand]Printing-Exporting
  [Expand]Data Editors
  [Expand]Navigation Controls
  [Expand]Spell Checker
  [Expand]Property Grid
  [Expand]PDF Viewer
  [Expand]TreeMap Control
  [Expand]Gantt Control
  [Expand]Diagram Control
  [Expand]Windows and Utility Controls
   Dialogs, Notifications and Panels
  [Expand]Scheduler (legacy)
 [Expand]Scaffolding Wizard
 [Expand]Localization
  Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Office File API
[Expand]Reporting
[Expand]Report and Dashboard Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation
View this topic on docs.devexpress.com (Learn more)

Date and Time Functions

This document describes the date and time functions implemented in the Spreadsheet.

Name

Description

Syntax

DATE

Returns a sequential serial number that represents a particular date.

DATE(year,month,day)

Year - A number that may include from one to four digits. If the year is between 0 (zero) and 1899 (inclusive), this value is added to 1900 to calculate the year. If the year is between 1900 and 9999 (inclusive), this value is used as the year. If the year is less than 0 or is 10000 or greater, the #NUM! error is returned.

Month - A positive or negative integer representing the month of the year. If the month is greater than 12, this number is added to the first month in the year specified. If the month is less than 1, this value plus 1 is subtracted from the first month in the year specified.

Day - A positive or negative integer representing the day of the month. If the day is greater than the number of days in the month specified, this number of days is added to the first day in the month. If the day is less than 1, this value plus 1 is subtracted from the first day of the month specified.

DATEDIF

Returns the number of days, months, or years between two dates.

DATEDIF(start_date, end_date, interval_type)

Start_date is the first date of the period.

End_date is the last date of the period.

Dates can be entered as text strings within quotation marks, as serial numbers, or as the results of other formulas or functions.

Interval_type is a case-insensitive string that specifies the type of the returned information:

  • "M" - Complete calendar months between dates.
  • "D" - Number of days between dates.
  • "Y" - Complete calendar years between dates.
  • "YM" - Complete calendar months between dates as if they were of the same year.
  • "YD" - Complete calendar days between dates as if they were of the same year.
  • "MD" - Complete calendar days between dates as if they were of the same month and year.

DATEVALUE

Converts a date's text representation into a serial number that represents a date.

DATEVALUE(date_text)

DAY

Returns an integer representing the day of the month (from 1 - 31).

DAY(date_serial_number)

Date_serial_number is a serial number representing a date.

Use functions such as DATE to supply an argument to the DAY function.

DAYS

Returns the number of days between two dates.

DAYS(end_date, start_date)

DAYS360

Returns the number of days between two dates, based on a 360-day year (twelve 30-day months).

DAYS360(start_date,end_date,[method])

Start_date and end_date are two dates between which you want to know the number of days.

Dates should be supplied as cell references, or as results of the DATE or other function that returns a sequential serial number representing a particular date.

Method is a logical value that specifies whether to use the US (NASD) method (if set to True) or European method (if set to False) in the calculation. If the method argument is omitted, the NASD method is used.

EDATE

Returns a date that is a specified number of months before or after a start date.

EDATE(start_date, months)

If months is not an integer, it is truncated. Dates should be supplied as cell references, or as results of the DATE or other function that returns a sequential serial number representing a particular date.

EOMONTH

Returns the last day of the month that is several months ahead or prior to the start_date.

EOMONTH(start_date, months)

If months is not an integer, it is truncated. Dates should be supplied as cell references or as results of the DATE or other function that returns a sequential serial number representing a particular date.

HOUR

Returns an integer that is the hour component of a specified time.

HOUR(time)

Time can be a text string representing time, the result of other functions (such as TIMEVALUE), or a decimal number. The decimal number is calculated as a portion of a date value (24 hours is 1.0, so 3 AM is 0.125 in decimal time).

ISOWEEKNUM

Returns number of the ISO week number of the year for a given date.

ISOWEEKNUM(date)

MINUTE

Returns an integer that is the minutes component of a specified time.

MINUTE(time)

Time can be a text string representing time, the result of other functions (such as TIMEVALUE), or a decimal number. The decimal number is calculated as a portion of a date value (24 hours is 1.0, so 3 AM is 0.125 in decimal time).

MONTH

Returns an integer that is the month component of a specified date.

MONTH(date_serial_number)

Date_serial_number is a serial number representing a date. Use functions such as DATE to supply an argument to the MONTH function.

NETWORKDAYS

Calculates the number of work days between two dates.

NETWORKDAYS(start_date,end_date,holidays)

Dates should be supplied as cell references or as results of the DATE or other function that returns a sequential serial number representing a particular date. All weekdays are included; weekends and dates identified as holidays are excluded, and not counted as working days. To specify the holidays to be excluded, use an optional third argument that is a range of cells containing dates or an array of serial numbers representing dates.

NETWORKDAYS.INTL

Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days.

NETWORKDAYS.INTL(start_date, end_date, weekend, holidays)

Start_date and end_date are required. They are the dates for which the difference is to be computed.

Weekend parameter is optional. It indicates the days of the week that are weekend days and are not included in the number of whole working days. Weekend is a weekend number or string that specifies when weekends occur. Weekend numbers are listed in the following table.

Weekend Number

Weekend Days

1 or omitted

Saturday, Sunday

2

Sunday, Monday

3

Monday, Tuesday

4

Tuesday, Wednesday

5

Wednesday, Thursday

6

Thursday, Friday

7

Friday, Saturday

11

Sunday only

12

Monday only

13

Tuesday only

14

Wednesday only

15

Thursday only

16

Friday only

17

Saturday only

Weekend string values are seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. For example, 0000110 would result in a weekend that is Friday and Saturday.

Holidays is the optional parameter. It is one or more dates that are to be excluded from the working days. Holidays should be a range of cells containing dates, or an array of the serial values.

NOW

Returns the current date and time as a serial number.

NOW()

SECOND

Returns an integer that is the seconds component of a specified time.

SECOND(time)

Time can be a text string representing time, the result of other functions, such as TIMEVALUE, or a decimal number. The decimal number is calculated as a portion of a date value (24 hours is 1.0, so 3 AM is 0.125 in decimal time).

TIME

Returns a decimal number that represents a specified time.

TIME(hour, minute, second)

Hour is a number from 0 (zero) to 32767. Any value greater than 23 will be divided by 24 and its remainder will be taken as an hour value.

Minute is a number from 0 (zero) to 32767. Any value greater than 59 will be converted into hours and minutes.

Second is a number from 0 (zero) to 32767. Any value greater than 59 will be converted into hours, minutes and seconds.

TIMEVALUE

Converts a text representation of time into a decimal number that represents time.

TIMEVALUE(time_text)

Date information in time_text is ignored. The decimal number is calculated as a portion of a date value (24 hours is 1.0, so 3 AM is 0.125 in decimal time).

TODAY

Returns a serial number of the current date.

TODAY()

WEEKDAY

Returns an integer representing the day of the week for a specified date.

WEEKDAY(date_serial_number,[return_type])

Date_serial_number is a serial number representing a date. Use functions such as DATE to supply an argument to the function.

Return_type is a number that specifies which integers are to be assigned to each weekday.

Return_type

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).

11

Numbers 1 (Monday) through 7 (Sunday).

12

Numbers 1 (Tuesday) through 7 (Monday).

13

Numbers 1 (Wednesday) through 7 (Tuesday).

14

Numbers 1 (Thursday) through 7 (Wednesday).

15

Numbers 1 (Friday) through 7 (Thursday).

16

Numbers 1 (Saturday) through 7 (Friday).

17

Numbers 1 (Sunday) through 7 (Saturday).

WEEKNUM

Returns an integer that is the week number of a specified date.

WEEKNUM(date_serial_number,[return_type])

Date_serial_number is a serial number representing a date. Use functions such as DATE to supply an argument to the function.

Return_type is a number that specifies a numbering system to use and a weekday that should be treated as the start of the week.

There are two systems used for this function. System one defines a week containing January 1 as the first week of the year. System two defines a week containing the first Thursday of the year as the first week of the year (ISO 8601, European week numbering system).

Return_type

Week begins on

System

1 or omitted

Sunday

1

2

Monday

1

11

Monday

1

12

Tuesday

1

13

Wednesday

1

14

Thursday

1

15

Friday

1

16

Saturday

1

17

Sunday

1

21

Monday

2

WORKDAY

Returns a date that is a specified number of working days (excluding weekends and holidays) before or after a start date.

WORKDAY(start_date, days, holidays)

All weekdays are counted; weekends and dates identified as holidays are excluded. To specify holidays to exclude, use an optional third argument that is a range of cells containing dates or an array of serial numbers representing dates.

WORKDAY.INTL

Returns the serial number of the date before or after a specified number of workdays with custom weekend parameters.

WORKDAY.INTL(start_date, days, weekend, holidays)

Start_date is a required parameter that specifies the start date. Days is a required parameter that specifies the number of workdays before or after the start_date. Weekend and holidays are optional parameters. For information on these parameters refer to the NETWORKDAYS.INTL description.

YEAR

Returns an integer that is the year component of a specified date.

YEAR(date_serial_number)

The year is returned as an integer in the range 1900-9999.

YEARFRAC

Returns the year fraction representing the number of whole days between start_date and end_date.

YEARFRAC(start_date, end_date, basis)

The basis is an optional parameter that specifies the type of day count. Basis values are listed in the following table.

Basis

Day count basis

0 or omitted

US (NASD) 30/360

1

Actual/actual

2

Actual/360

3

Actual/365

4

European 30/360

Is this topic helpful?​​​​​​​