How to use Excel's VALUE function

Excel's VALUE function is is a convenience feature used to convert from text strings to numerical values within a formula or cell. VALUE is primarily a convenience feature used in special edge cases — most of the time, Excel will perform the text-to-value conversions you need automatically, making use of the VALUE function unnecessary.

How VALUE works

The syntax of the VALUE function is as follows:

=VALUE(text)

VALUE will look at the text string you provide, and, if it recognizes a number, will convert that number to a value that can be used in formulas and other numerical calculations.

Here are a couple examples of VALUE in action:

=VALUE("4")
Output: 4

=VALUE("34")
Output: 34

=VALUE("109")
Output: 109

Notice that in each of these examples, VALUE outputs a number, not a string.

VALUE with dates

When VALUE is used on a string that Excel recognizes as a date or date-time combination, it outputs the VALUE of that date as the number of days since January 1, 1900. This is a special feature that can be used to add and subtract dates to find the number of days between them. Take a look at the following example:

=VALUE("5/4/2025")
Output: 45781

The output to the above formula is 45781, because there are 45,781 days between January 1, 1900 and May 4, 2025. Likewise, we could write the same formula with a more complex textual representation of a date:

=VALUE("May 4, 2025")
Output: 45781

VALUE will recognize and appropriately handle any text string formatted as a recognizable number, date, or date-time.

VALUE with times

Let's tack on a time to the end of the string in the example above to show how VALUE deals with times:

=VALUE("5/4/2025 6:00")
Output: 45781.25

The above formula outputs 45781.25, because the time 6:00 is one quarter (0.25) of the way through the day.

Note that date-time combinations must be given in 24-hour format. Excel will not recognize variants that stray even mildly from its accepted date-time formats:

=VALUE("5/4/2025 6:00PM")
Output: #VALUE!

Other acceptable formats

VALUE also works with a number of other formats, including percentages and scientific notation:

=VALUE("65%")
Output: 0.65

=VALUE("6E-2")
Output: 0.06

When should I use VALUE?

In most normal Excel scenarios, VALUE is not necessary, because Excel automatically converts numbers stored as strings to numerical format when conducting calculations. However, if you find that other numerical functions such as SUM and AVERAGE aren't performing as expected — especially when you are trying to run them on data from an external source — consider running your input columns through VALUE to ensure that Excel is appropriately recognizing them as numbers.

Comments