This tutorial explains how to convert a date to a number in three different scenarios:
1. Convert One Date to Number
2. Convert Several Dates to Numbers
3. Convert Date to Number of Days Since Another Date
Let’s jump in!
Example 1: Convert One Date to Number
Suppose we would like to convert the date “4/10/2022” to a number in Google Sheets.
We can use the DATEVALUE function in Excel to do so:
By default, this function calculates the number of days between a given date and 1/1/1900.
The following screenshot shows how to use this function in practice:
This tells us that there is a difference of 44,661 days between 4/10/2022 and 1/1/1900.
Example 2: Convert Several Dates to Numbers
Suppose we have the following list of dates in Google Sheets:
To convert each of these dates to a number, we can highlight the range of cells that contain the dates, then click the Format tab, then click Number from the dropdown menu, then click Automatic:
This will automatically convert each date to a number that represents the number of days between each date and 1/1/1900:
Example 3: Convert Date to Number of Days Since Another Date
We can use the following formula to convert a date to a number of days since another date:
=DATEDIF(B2, A2, "d")
This particular formula calculates the number of days between the date in cell B2 and the date in cell A2.
The following screenshot shows how to use the DATEDIF formula to calculate the number of days between the dates in column A and 1/1/2022:
Here’s how to interpret the values in column B:
- There are 3 days between 1/1/2022 and 1/4/2022.
- There are 5 days between 1/1/2022 and 1/6/2022.
- There are 14 days between 1/1/2022 and 1/15/2022.
And so on.
The following tutorials explain how to perform other common tasks in Google Sheets:
How to Sort by Date in Google Sheets
How to Extract Month from Date in Google Sheets
How to Convert Timestamp to Date in Google Sheets