In Google Sheets, sometimes you will need to convert dates in your spreadsheet to numbers. You can do this easily by either using a formula, or by formatting the cells. I’ll walk you through both methods, and I’ll show you how to convert single dates and whole columns of dates into numbers.
In Google Sheets, dates are actually just numbers (unless the date are converted to text). Each day gets a unique number, and each new day the number increments by 1.
To convert dates to numbers in Google Sheets, follow these steps:
- Select the cell or cells containing the date you want to convert to a number
- Click on the “More formats” menu in the top toolbar (The button says “123”)
- Click “Number”
After following the steps above, the dates in the selected cells will be converted into number format, and instead of the cell displaying a date, it will display a number like this: “45228”
For example, if you enter the date “10/19/2023” in cell A2, select cell A2, then change the format of the cell to “Number”, the cell will display the number 45228. The same number would have appeared if the date was originally displayed like “October 29, 2023“
To convert dates to numbers with a formula, use the DATEVALUE function like this: =DATEVALUE(A2)
Now let’s go over some detailed examples of how to convert dates to numbers in Google Sheets, using multiple methods.
Using the DATEVALUE function to convert a date to a number
With the DATEVALUE function, you can automatically convert dates to numbers.
To use the DATEVALUE function, you simply specify the cell that contains the value that you want to convert to a number, and the formula will automatically convert the date to a number for you.
The Google Sheets TEXT function description:
Syntax:
DATEVALUE(date_string)Formula summary: “Converts a provided date string in a known format to a date value.”
To use the DATEVALUE function to convert a date to a number, follow these steps:
- Select the cell containing the date you want to convert.
- In a new cell, type the following formula: =DATEVALUE(A2), where “A2” is the cell reference with the date you want to transform.
After following the steps above, the cell with the DATEVALUE formula will display the numerical representation of the date in the selected cell.
In this example, we have a date in cell A2, which is “10/29/2023.”
What we are going to do is use the DATEVALUE function to convert this date into a number.
To do this, we will use the formula =DATEVALUE(A2) to transform the date in cell A2 into a numerical value.
=DATEVALUE(A2)
As you can see in the image above, after applying the DATEVALUE formula, cell B2 now displays the number “45228“, which corresponds to the date “10/29/2023.”
Format the cells as “Number” to convert a date to a number
A very simple way that you can convert dates to numbers in a spreadsheet, is by simply changing the format of the cells to “Number” format.
When you do this, Google Sheets will convert the date displayed in the cell to a number.
To format the cells to convert dates to numbers, select the cell or cells containing the date(s) that you want to convert to numbers, click on the “More formats” menu in the top toolbar (The button says “123”), then click “Number”.
After following the steps above, the selected date(s) will now display as a number in the cell.
In this example, we have a list of dates in column A that we want to convert into numbers. You’ll notice that the same date is entered into each cell in column A, except that date is formatted differently each time. This is to show you that all of these different date formats that represent the same date, will convert into the same number.
What we are going to do is use the “Number” cell format to convert these dates into numerical values. To do this, we will select the cells with the dates in column A, go to the “More formats” menu, and choose “Number.”
As you can see in the image above, after applying the “Number” format to the cells and column A, all of the cells in column B will now display the number 45228, which corresponds to the date “10/29/2023.”
After converting cell format to “Number”, the numbers will have two decimal places. You can select the cells with the numbers, then click the “Decrease decimal places” button twice to show whole numbers without a decimal.
Converting entire columns of dates to numbers in Google Sheets
If you want you can quickly copy your DATEVALUE formula down the column to convert dates to numbers for an entire column, or you can set the format in entire columns to “Number”.
To convert entire columns of dates to numbers, follow these steps:
- Select the entire column containing the dates you want to convert
- Click on the “More formats” menu at the top (The button that says “123”)
- Choose “Number” from the list of formatting options.
After following the steps above, all the dates in the selected column will be displayed as numerical values.
To copy the DATEVALUE function down a column, follow these steps:
- Click on the cell containing the DATEVALUE function that you want to copy
- Hover your cursor over the small square or dot (the “fill handle”) at the bottom-right corner of the selected cell
- Click and drag the fill handle down to copy the function to the desired number of cells in the column
- Release the mouse button, and Google Sheets will automatically adjust the cell references in the copied functions, so they work with the corresponding dates in each row
Click here to learn more about copying formulas in a Google spreadsheet.
Or click here to learn how to use the ARRAYFORMULA function to apply a single formula to an entire column.
Click here to get your Google Sheets cheat sheet
Or click here to take the dashboards course
