Google Sheets comes with powerful functions to organize and analyze datasets by date. You can convert a serial number to date using the TO_DATE function in Google Sheets. Similarly, you need to use the “DATEVALUE” function to convert a date to serial numbers or text string.
Sometimes, it is quite overwhelming to download the datasets from an online source and see that the dates have been converted to numbers.
So, are you also having the same issues while exporting or downloading the spreadsheets containing the dates?
No worries! Because we have got your back.
In this article, let us look at the steps to convert a text string to date and vice versa. Make sure to read this article until the end because it contains a lot of helpful information to organize and analyze the Google spreadsheets by dates.
Why convert Text String to Date?
As dates can be written in various formats, this is a common problem that you will face in both Google Sheets and Microsoft Excel.
Though we may easily interpret dates written in any format as human beings, they will surely confuse spreadsheet software like Google Sheets. It leads to errors while performing calculations, especially sorting and filtering the datasets by date.
We are about to discuss the following conversions.
- Convert Text String to Date
- Convert Serial Number to Date
- Convert Date to Serial Number
Download the Example Google Sheet
Before we proceed further in this article, you can consider downloading the following Google Sheet that contains the datasets we will use to demonstrate the TO_DATE and DATEVALUE functions in Google Sheets.
It will strengthen your knowledge of these formulas through practice.
You can skip downloading the above spreadsheets if you have your own ready to practice the things we are about to discuss.
How to Convert Text to Date in Google Sheets
After exporting the datasets from an online source or PDF file, you may notice that the dates are in text format.
Consider the following example: Here, the hiring dates are represented as text strings.
Task: Convert the hiring dates in text format to valid dates that are acceptable by Google Sheets. We have a total of 20 records to convert, as shown above.
For the sake of this article, we will divide the process into three steps.
Step #1 – Converting the Text String to a Serial Number using the DATEVALUE function
You first need to convert the text string to a serial number using the DATEVALUE function in Google Sheets.
The DATEVALUE is a simple function in Google Sheets. The general syntax is as follows:
=DATEVALUE(date_string)The “date_string” argument must be replaced with the text string you wish to convert to date. You can simply provide the cell reference that holds the text string.
It is clear that you don’t need any expert knowledge to use the DATEVALUE function.
So, let us convert our text string to a serial number using the DATEVALUE function.
- Open the Google Sheet
- Go to the spreadsheet containing the dates dataset
- Select the cell and type “=datevalue“
- Choose the first option from the popup or press “Tab” on your keyboard
- Provide the cell reference value “B2“
- Close the brackets using “)” on your keyboard
- Press “Enter“
The final formula should look like this:
=DATEVALUE(B2)
It will instantly convert the text string to a serial number. To apply this formula to the rest of the dates, hover to the lower-rightmost corner of the cell where we just used the DATEVALUE function. You will see a “+” icon.
Click on that “+” icon and drag the formula for the rest of the cells.
Spreadsheet software like Google Sheets and Microsoft Excel use these serial numbers to perform various operations and date calculations. At first sight, we as users may see and understand the dates in Google Sheets in various formats. However, spreadsheet software understands the dates based on these serial numbers.
PRECAUTION: Note that the DATEVALUE function will work for all the valid date formats acceptable by Google Sheets. If you face any error while applying the DATEVALUE function, make sure to check the date format.
Here is an example,
The first three dates are valid, so the function has returned the serial numbers, and the last two are invalid.
Step #2: Converting the Serial Number to Date using the TO_DATE function
Now, we will convert the serial number we obtained in the first step to date. Google Sheet offers an effortless function called TO_DATE for this purpose.
The general syntax for the TO_DATE function is as follows,
=TO_DATE(value)Here, the “value” argument accepts the serial number representing a date in Google Sheets. You can either manually type the serial number or provide the cell reference containing that serial number.
The TO_DATE function is pretty straightforward to use.
- Type “=to_date”
- Select the first option from the dropdown or press “Tab” on your keyboard
- Our first serial number is in C2. So, we will replace the “value” argument with the cell reference “C2“
- Complete the bracket using the “)“
- Press “Enter” on your keyboard
The final formula should look like this:
=TO_DATE(C2)In our case, the converted date follows the United States format: month, date, and year (mm/dd/yyyy).
We’ll discuss changing the default date format by changing the Google Sheet’s location to the desired country in the last section of this article.
To apply the above formula to the rest of the cells, you can make use of the Flash Fill feature in Google Sheets or simply the “+“ icon, which is displayed after hovering your mouse cursor to the lower-rightmost side of the first cell where the formula is applied previously.
Step #3 – Changing the Date Format
You can skip this step if you are okay with the default date format obtained by converting the serial numbers to dates.
But we highly suggest exploring the available date formats in Google Sheets. A perfect date format plays a crucial role in visual data analysis.
You can follow the steps below to change the date format in Google Sheets.
- Select all the cells containing the dates
- Hover to the main menu and click on the “Format” tab
- Choose “Number” from the dropdown
- Next, select the “Custom date and time” option
- A new dialog box will open, as shown in the following image
- Choose a date format from the list of available options
- Click on the “Apply” button in green
Note that you can choose from tens of date formats predefined in Google Sheets. Additionally, it is possible to create your unique date format using the following section of the dialog box you saw in step 5.
For date, you can choose one of the following formats.
Similarly, for the month, you have the following options.
Years can be represented as two-digit (23) or a full numeric year (2023).
How to Convert a Date to Text String in Google Sheets
You can simply change the cell’s formatting to convert a date to a text string.
Follow the steps below,
- Hover to the main menu and click on “Format” tab
- Select the “Number” option from the popup
- Next, click on the “Plain Text“
All your dates are now converted to the respective text strings. You can check the results by double-clicking on any of the cells. You may have seen that a calendar popup is displayed after clicking on the cell containing a date that follows a valid date format.
In our case, as we have converted the dates to text strings, you won’t see any calendar popup after double-clicking on any cell.
How to Convert an 8-digit Number to Date in Google Sheets
You may have seen that the dates are represented by eight digits, as shown in the following image when you import the dataset from other documents, including PDF.
To convert those 8-digit numbers to date, you must start by extracting the day, month, and year.
Extracting the day, month, or year from those eight digits is nothing but getting certain characters from a text string. So, for this purpose, we will make use of the LEFT, MID, and RIGHT functions by the Google Sheets.
Let us discuss each of these functions shortly,
LEFT function
It is useful when you wish to extract the characters that are at the beginning of the text string.
The general syntax for the LEFT function in Google Sheets is as follows.
=LEFT(string, [number_of_characters])Here is the explanation for each argument of the LEFT function,
- “string” – This argument needs to be replaced with the text string from where we wish to extract the characters
- “number_of_characters” – As the name suggests, this argument lets you define the number of characters you wish to extract from a given string
MID function
In contrast to the LEFT function, the MID function extracts the characters in the middle of the text string.
The general syntax for the MID function in Google Sheets is as follows.
=MID(string, starting_at, extract_length)Here is the explanation for each argument of the LEFT function,
- “string” – It represents the text string from where we wish to extract the characters
- “starting_at” – Here, you need to put the number at which the first character you wish to extract is positioned in the text string
- “extract_length” – This argument lets you define the number of characters you wish to extract from the first character you specified using the previous argument
RIGHT function
The LEFT and RIGHT functions in Google Sheets are identical. The difference between them is pretty obvious from their name itself.
The RIGHT function extracts the characters that are at the end of the text string.
The general syntax for the RIGHT function in Google Sheets is as follows.
=RIGHT(string, [number_of_characters])Here is the explanation for each argument of the LEFT function,
- “string” – Replace it with the text string from where we wish to extract the characters
- “number_of_characters” – Enter the number of characters you wish to extract from a given string
After extracting the day, month, and year from the 8-digit number, you need to use the DATE function by Google Sheets to create a valid date, which the spreadsheet software accepts.
DATE function
The DATE is a simple function in Google Sheets with the following syntax,
=DATE(year, month, day)The arguments of the above formulas are self-explanatory.
Task: Convert the Text to Date where the Date is represented by an 8-digit Number Format as shown in the following image
By carefully observing most of the 8-digit numbers, you may have seen that the first two digits represent the month, followed by them, the next two digits represent the day, and the last four digits represent the year.
As discussed, we will use a combination of functions, including DATE, RIGHT, MID, and LEFT.
Step #1 – Get the year from an 8-digit number
As the year is at the end of the text string, we need to use the RIGHT function.
For the first eight digits placed in the cell “A1“, our formula will be as below.
=RIGHT(A1,4)It will extract the text string’s last four characters, representing the year.
Step #2 – Get the month from the 8-digit number
The first two digits represent the month. So, let us use the LEFT function to extract them.
Considering the first eight digits are in cell “A1“, our final formula will be as follows.
=LEFT(A1,2)Step #3 – Get the day from an 8-digit number
The day is in the middle. So, we will use the MID function.
As the day is available at the 3rd and 4th position, we will replace the “starting_at” argument with the number “3” and “extract_length” with the number “2”.
Our final formula considers the first eight digits from the cell “A1“,
=MID(A1,3,2)Step #4 – Combine the RIGHT, MID, and LEFT functions with the DATE function to get a valid date
We will combine all of the formulas we have used between steps 1 to 3 with the DATE function as below.
=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,3,2))This formula will return a valid date in default (United States) format as month, day, and year (mm/dd/yyyy).
How to change the default date format
In our case, the default date format is set to the United States. But if you are from the United Kingdom or any other country, you can change the default date format to your country.
The US follows the month, day, and year format (mm/dd/yyyy), and the United Kingdom follows the day, month, and year format (dd/mm/yyyy).
Let us change the default date format from the US to the UK.
- Hover to the main menu and click on “File“
- Select “Settings” from the popup
- To select the United Kingdom, click on the dropdown below the “Locale” option
- Click on the “Save and reload” button
Note that the browser will reload the page, and you will notice that all the dates are now represented as day, month, and year (dd/mm/yyyy).
Conclusion
Thank you! That’s all when it comes to converting the text string to a date and vice versa.
Google Sheets is a popular spreadsheet program used by many professionals. It boasts various functions to organize and manage datasets by date.
Converting a text string to a valid date format is crucial for effective data analysis.
I hope this article taught you all the bells and whistles related to converting a text string to a date.
Comment below if you still need help somewhere or are having a particular error while converting text strings to dates. I will try to answer all of your questions as soon as possible.