Google Sheets: The VALUE Function (An Easy Guide)

In this article, we will look at how to use the VALUE function in Google Sheets.

It is one of the simplest functions available in the application and its uses are quite niche.

That said, the primary use of the VALUE function is to extract the underlying numerical data from a value.

But before we dive into the examples and uses, let’s first get to know the syntax and basics of the VALUE function of Google Sheets.

The Basics of the VALUE Function of Google Sheets

The syntax of the VALUE function:


the value function syntax in google sheets

As you can see from the syntax, the VALUE function takes a text value as a parameter. If Google Sheets recognizes an underlying number value, then that number value is extracted.

The following image shows a few examples of passing different parameters through the value function:

the number values extracted by the value function in google sheets

3 Examples of Using the VALUE Function in Google Sheets

1. Primary Use: Convert Numbers Formatted as Text to Numbers

As we have seen in the previous section, the VALUE function takes text values as parameters to extract any underlying number values that can be recognized by Sheets.

Consider the following dataset where all the values are in the text format:

numbers in the text format

While rare, a situation like this may occur when importing data or due to the wrong formatting of cells in the worksheet.

Either way, we can use the VALUE function to extract the number values from these texts:


extracting the number value using the value function in google sheets

As you can see, even if we have different number formats (decimals, times, and dates) or if the numbers are formatted as text, as long as Google Sheets recognizes them, as it most often does, the VALUE function will work.

Tip: You can use the ARRAYFORMULA function to pass all the adjacent data as an array and present the results simultaneously.


updating the value formula with arrayformula function

This will help save time and the processing power of the application.

2. Convert a Date Formatted as Text to a Number

We all know that there are multiple formats of date that are considered valid in Google Sheets. Some of these can be in text format.

For example, when using the TEXT function to reformat the date in another location, like a different worksheet, the result is presented as a text. We can see this in the following image.

dates imported and formatted as text

Now, with the VALUE function, we can extract the underlying date value of these date texts.


extracting the underlying date values with value function from a text date in google sheets

On their own, these values don’t make much sense since these are how Google Sheets views date values.

However, we’ve achieved what we wanted: extract the number value from the date.

Now, all we have to do is make sense of this numerical value, we will use the TO_DATE function to do so:


resetting the date value to a proper date format using the to_date function

You can also combine the formulas to:


Note: If no year value is given, these functions will assume the current year.

Alternatively, we have an offshoot of the VALUE function made specifically to extract date values from a string called the DATEVALUE function.

the datevalue function gives the same output as the value function

3. Convert Time Formatted as Text to a Number

Another unique number format is time. Fundamentally, according to Google Sheets, a 24-hour period is equivalent to 1.

So, if we are to pass a time value, whether it be a number or text, through the VALUE function then we’ll get a fraction depending on the time.

Here are a few examples of passing different time formats including text values. The general VALUE formula remains the same:


extracting the time value as a fraction of 24 hours using the value function in google sheets

Like dates, VALUE also has a time version of its function called the TIMEVALUE function.

It operates similarly to VALUE but it resets every 24 hours.

Here’s the side-by-side comparison of the results:

comparison between the results of timevalue and value functions

Final Words

That concludes our simple guide on how to use the VALUE function in Google Sheets. It is a fairly simple function with niche use cases, though those few cases are quite important, especially when extracting date and time values for calculations.

Feel free to leave any queries or advice you might have in the comments section below.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply