Open In App

How To Convert Date To Number String Or Text Format In Excel?

Improve
Improve
Like Article
Like
Save
Share
Report

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

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.

Choosing-number-option-from-home

 

Step 2: The cell format then changes into the original serial number internally stored by Excel.

Cell-format-changes

 

Note: The first serial number stored by excel is of date 01/01/1900.

First-serial-number-stored

 

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
  1. d
  2. dd
  3. ddd
  4. dddd
  1. Number without leading 0 (Eg: 6)
  2. Number with leading 0 (Eg: 06)
  3. Day in short form (Eg: Mon)
  4. Day in full form (Eg: Monday)
Month
  1. m
  2. mm
  3. mmm
  4. mmmm
  1. Month number without leading 0 (Eg: 8)
  2. Month number with leading 0 (Eg: 08)
  3. Month three-letter abbreviation (Eg: Jan)
  4. Month full form (Eg: January)
Year
  1. yy
  2. yyyy
  1. Year in two-digit (Eg: 16)
  2. Year in four-digit (Eg: 2016)
Separator
  1. / (slash)
  2. – (dash)
  3. . (dot)
  4. , (comma)
  1. 10/10/2020
  2. 10-10-2020
  3. 10.10.2020
  4. 10 Oct, 2020

Text-function-applied

 

Formula-for-date-to-text

 

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.

After-adding-apostrophe-in-formula-bar

 

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. 

Selecting-format-cell-option

 

Step 2: In the Custom option of the Number Tab, specify the format. Click Ok.

Specifying-format

 

Format-cell-result

 

Text to Column

Step 1: Select the cells. Go to the Data tools in the Data tab. Click on Text to Column. 

Selecting-text-to-column-from-data-tools

 

Step 2: In the next widget, choose delimited and click next.

Choosing-delimited-option

 

Step 3: In the next screen, deselect all the delimiters and click next. 

Deselecting-delimiters

 

Step 4: Finally, enter the destination cell number and click finish.

Entering-destination-cell

 

Note: The Text to Column tool will always convert dates to the short date format according to your system.

Text-to-column-result

 

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.

Pasting-content-in-notepad

 

Step 2: The target cells should be chosen and changed to the Text Cell Format in the next step.

Target-cells-changed-to-text-format

 

Step 3: Finally, paste the content in the target cells. It will be in the text format as shown in the figure.

Notepad-result

 

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.

Clicking-options-from-file-menu

 

Step 2: Select Customize Ribbon from Options. Check the Developer box from the right menu.

Go-to-customize-ribbon

 

VBA is now enabled. We can access VBA Editor through the Developer tab or the keyboard shortcut ALT + F11.

VBA-enabled

Visual Basic from Developer Tab

Step 3: Insert Module in the workbook. 

Inserting-module

 

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

Adding-code-to-text-function

 

Step 5: In the next Dialog Box, click No and change the Extension to “Excel Macro-Enabled Workbook” to save it.

Saving-macro-enabled-workbook

 

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.

Inserting-shape

 

Step 7: Right-click the text box and choose “Assign Macro” for the toText code to make it a clickable button. 

Assigning-macro

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.

Result-through-vba

 



Last Updated : 16 Nov, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads