Excel Function: VALUE

Purpose

The VALUE function converts a value with a data type of date, (decimal) number, time, text or duration to a number. A text value passed to the VALUE function must (1) consist of numbers, digit grouping symbols, decimal symbols and currency symbols arranged so it conforms to the local number & currency formatting rules, or (2) be interpretable as being either a date, time or duration.

Invocations of the VALUE function take the form:

VALUE(text)

Arguments

Argument	Argument type	Description
text	Mandatory	A value with a data type of date, number, time, text or duration to be converted into a number.

Related Functions

Function	Example	Purpose

Error Conditions

Error value	Condition type	Condition
#VALUE!	Bad argument	The value supplied to the text argument could not be converted to a number.

Examples: Explanatory Notes

• Results of examples demonstrating the behaviour of VALUE when operating on values of type currency, geography, money and stock were generated with the formulae VALUE(<text>,0).
  • <text> represents a cell containing an example’s “‘Text’ argument“.
• Number & currency formatting was configured as follows:
  • Period (“.”) was the decimal symbol.
  • Comma (“,”) was the digit grouping symbol.
  • Digit grouping was 123,456,789.
  • Pounds stirling (“£”) was the currency symbol.
• Knowledge of how Excel stores dates is needed to understand Example 2.

Example 1: Converting Text to Numbers

• Example 5’s result is an error because digit grouping rules were not observed.
• Example 7’s result is an error because “£”, not “$”, is designated as the currency symbol.
• Example 9’s result is 0.5 because 50% of a day has passed when the time reaches 12:00:00.

Example	Formula	Result	Description
1	=VALUE(1)	1	Convert text representing a single digit integer to a number.
2	=VALUE(1.1)	1.1	Convert text representing a number with 1 digit after the decimal point to a number.
3	=VALUE("1.00E+10")	10000000000	Convert text representing a number expressed in scientific notation to a number.
4	=VALUE("3,000,000.12")	3000000.12	Convert text representing a number and containing digit grouping symbols to a number.
5	=VALUE("3,0000,00.12")	#VALUE!	Convert text containing misplaced digit grouping symbols to a number.
6	=VALUE("£3,000,000.12")	3000000.12	Convert text representing a monetary amount to a number.
7	=VALUE("$3,000,000.12")	#VALUE!	Convert text representing a monetary amount to a number.
8	=VALUE("F")	#VALUE!	Convert a letter (which represents 15 in hexadecimal) to a number.
9	=VALUE("12:00:00")	0.5	Convert text representing a time to a number.

Example 2: Converting Dates to Numbers

Example	Formula	Result	Description
1	=VALUE(DATE(2023,5,7))	45053	Convert a date to a number.

Example 3: Converting Times to Numbers

Example	Formula	Result	Description
1	=VALUE(TIME(12,0,0))	0.5	Convert a time to a number.
2	=VALUE(TIME(18,0,0))	0.75	Convert a time to a number.

Example 4: Converting Durations to Numbers

Example	Formula	Result	Description
1	=VALUE(TIME(18,0,0) - TIME(12,0,0))	0.25	Convert a duration to a number.

Example 5: Converting Currencies to Numbers

Example	‘Text’ argument	Result	Description
1	USD/GBP	#VALUE!	Convert a stock to a number.

Example 6: Converting Geographies to Numbers

Example	‘Text’ argument	Result	Description
1	New York	#VALUE!	Convert a geography to a number.

Example 7: Converting Money to Numbers

Example	‘Text’ argument	Result	Description
1	£3000	3000	Convert a monetary amount denominated in £ to a number.
2	$3000	#VALUE!	Convert a monetary amount denominated in $ to a number.

Example 8: Converting Stocks to Numbers

Example	‘Text’ argument	Result	Description
1	MICROSOFT CORPORATION (XNAS:MSFT)	#VALUE!	Convert a stock to a number.