Documentation/Calc Functions/VALUE
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
VALUE
Category:
Text
Summary:
Converts the string representation of a number to numeric form. If the supplied string is a valid date, time, or date-time, the corresponding date-time serial number is returned.
Syntax:
VALUE(Text)
Returns:
Returns a number that corresponds to the supplied string.
Arguments:
Text is a text string (in quotation marks), a number, or a reference to a cell containing one of those types, that contains the value to be converted.
- If Text is the empty string, then VALUE reports an invalid argument error (Err:502).
- If Text does not contain a number, date, time, or date-time in a recognized format (which may depend on locale), then VALUE reports an invalid argument error (Err:502).
Additional details:
The detailed behavior of the VALUE function depends on your selected locale and the following guidelines provide more information.
Locale-independent behaviors
The following guidelines apply for any locale:
- VALUE accepts a range of number formats that do not include decimal separators or digit group separators. Examples include: "+1234" (the "+" character may be omitted), "-345", "2e+3" (the "+" character may be omitted), "-2E-1", "50%" (values that end in the "%" character are divided by 100).
- VALUE accepts fractional values, specified as an integer followed by a space and then the fraction. Examples include "-1 2/5", "+7 15/16" (the "+" character may be omitted), "0 1/2" (values between 0 and 1 are specified in this format by including a 0 integer part).
- VALUE accepts times in "hh:mm:ss" or "hh:mm" format.
- VALUE accepts dates in the ISO 8601 "YYYY-MM-DD" format.
- VALUE accepts date-times in the ISO 8601 "YYYY-MM-DDThh:mm:ss" format and the related "YYYY-MM-DD hh:mm:ss" format.
Locale-dependent behaviors
On a page like this, it is impractical to capture every possibility that arises for every potential locale. Therefore, the following additional guidelines refer to the behavior of the VALUE function with the English(USA) locale selected. These should allow you to experiment to establish additional behaviors for your locale.
- VALUE accepts a range of number formats that include decimal separators (full stop, Unicode U+002E) or digit group separators (comma, Unicode U+002C). Examples include: "+1,234.567" (the "+" character may be omitted), "-12,345.678", "+2.345e+3" (both "+" characters may be omitted), "-2.345E-1", "50.55%" (values that end in the "%" character are divided by 100), "$45.67" (the "$" character, representing the US currency unit, is ignored).
- VALUE accepts dates that include month names. Examples include "Jul 30, 2021" (using 3-character abbreviated month names) and "July 30, 2021" (using full month names).
- VALUE accepts dates in the "MM/DD/YYYY" and "MM/DD/YY" formats.
- VALUE accepts times that include fractional seconds in "hh:mm:ss.ss" format.
Examples:
The following examples all refer to the English(USA) locale.
| Formula | Description | Returns |
|---|---|---|
| =VALUE("1234") | - | 1234 |
| =VALUE(1,234) | Here VALUE accepts a numeric argument with no quotation marks. | 1234 |
| =VALUE(D1) where cell D1 contains 1234 (formatted as either a number or a string). | Here the argument is passed as a cell reference. | 1234 |
| =VALUE("+1,234.567") | Here the argument contains both a decimal point and a digit group separator, with the latter being removed. | 1,234.567 |
| =VALUE("-12,345.678") | Here the argument contains both a decimal point and a digit group separator, with the latter being removed. | −12,345.678 |
| =VALUE("+2.345e+3") | Here the argument uses scientific notation, with a lowercase "e". | 2345 |
| =VALUE("-2.345E-1") | Here the argument uses scientific notation, with an uppercase "E". | −0.2345 |
| =VALUE("50.55%") | Here the argument contains a trailing percent (%) character, so that the supplied number is divided by 100. | 0.5055 |
| =VALUE("$45.67") | Here the leading dollar ($) character is removed. | 45.67 |
| =VALUE("-1 2/5") | Here the argument is a negative fractional value. | −1.4 |
| =VALUE("+7 15/16") | Here the argument is a positive fractional value – the plus (+) character is removed. | 7.9375 |
| =VALUE("0 1/2") | Here the argument is a fractional value. | 0.5 |
| =VALUE("09:20:25") | Here a time in hh:mm:ss format is converted to a date-time sequence number. | 0.389178240740741 |
| =VALUE("09:20") | Here a time in hh:mm format is converted to a date-time sequence number. | 0.388888888888889 |
| =VALUE("09:20:25.765") | Here a time that includes fractional seconds is converted to a date-time sequence number. | 0.389187094907407 |
| =VALUE("2021-07-30") | Here a date in YYYY-MM-DD format is converted to a date-time sequence number. | 44407 |
| =VALUE("July 30, 2021") | Here a date with month text is converted to a date-time sequence number. | 44407 |
| =VALUE("Jul 30, 2021") | Here a date with abbreviated month text is converted to a date-time sequence number. | 44407 |
| =VALUE("07/30/2021") | Here a date in MM/DD/YYYY format is converted to a date-time sequence number. | 44407 |
| =VALUE("07/30/21") | Here a date in MM/DD/YY format is converted to a date-time sequence number. | 44407 |
| =VALUE("2021-07-30T09:20:25") | Here a date and time in ISO8601 format is converted to a date-time sequence number. | 44,407.3891782407 |
| =VALUE("2021-07-30 09:20:25") | Here a date and time in a format similar to ISO 8601 (without the interior "T" character) is converted to a date-time sequence number. | 44,407.3891782407 |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
VALUE