Excel’s built-in date system by default supports dates between January 1, 1900, and December 31, 9999. In this range, the numbers start at 1 and increase by 1 each time. In addition to text and numeric data types, Excel also allows dates that are internally stored as serial numbers. In Excel, the date format is typically recognized by default. However, Excel also allows you to input date format by choosing the cell and pressing (Ctrl+;).
Methods to Convert Date to Number String
DATEVALUE Function
Syntax: =DATEVALUE (date_text)
Where date_text is the only argument
DATEVALUE Function
Cell Formatting
Step 1: Once the cell with the value has been chosen, click the home tab and choose the NUMBER or TEXT option.
Step 2: The cell format then changes into the original serial number internally stored by Excel.
Note: The first serial number stored by excel is of date 01/01/1900.
Methods to Convert Date to Text
TEXT Function
It is used to convert a date into a specified text format.
Syntax: =TEXT(value, format_text)
For today’s date: =TEXT(TODAY(), format_text)
Where value: The value that needs to be converted. This could be a numeric cell reference or value, or a numeric value returned by a formula.
format_text: The format to display the number. It should be in double quotes.
There are different formats possible for the day, month, year, and separator.
|
Value |
Meaning |
|
|---|---|---|
| Day |
|
|
| Month |
|
|
| Year |
|
|
| Separator |
|
|
Adding Apostrophe
An apostrophe can be added in the formula bar where the date is entered to make it text. In some excel versions, adding space in the formula bar adds the apostrophe itself.
Format Cell Option
Step 1: Either select the cells, right click and click on the FORMAT CELL option, or in the Home Tab, select Cell then format and click on the FORMAT CELL.
Step 2: In the Custom option of the Number Tab, specify the format. Click Ok.
Text to Column
Step 1: Select the cells. Go to the Data tools in the Data tab. Click on Text to Column.
Step 2: In the next widget, choose delimited and click next.
Step 3: In the next screen, deselect all the delimiters and click next.
Step 4: Finally, enter the destination cell number and click finish.
Note: The Text to Column tool will always convert dates to the short date format according to your system.
Copy-Paste Through Notepad
Step 1: Copy the selected cells with date and paste them to Notepad. The dates are instantly transformed into text when pasted into the notepad.
Step 2: The target cells should be chosen and changed to the Text Cell Format in the next step.
Step 3: Finally, paste the content in the target cells. It will be in the text format as shown in the figure.
VBA
VBA is located in Excel’s “Developer” tab.
Step 1: The “Developer” tab must first be enabled before it can be accessed from the ‘File’ option.
Step 2: Select Customize Ribbon from Options. Check the Developer box from the right menu.
VBA is now enabled. We can access VBA Editor through the Developer tab or the keyboard shortcut ALT + F11.
Visual Basic from Developer Tab
Step 3: Insert Module in the workbook.
Step 4: Add the following code to convert Date to Text. Name the function ToText in the blank next to General. Click on Run Button.
Sub toText()
Dim d as Range
For Each d in Selection
d.Value = Format(d.Value, “dd.mm.yyyy”)
Next d
End Sub
Step 5: In the next Dialog Box, click No and change the Extension to “Excel Macro-Enabled Workbook” to save it.
Step 6: Macros in VBA are a collection of codes that form a programming language. In the excel sheet, add a macro button for the above code by adding a shape/ text box. Add the text “Convert to Text” in the shape.
Step 7: Right-click the text box and choose “Assign Macro” for the toText code to make it a clickable button.
Step 7: Assign macro to the button
Finally, using the created button, we can change the date to text in the format specified in the code.