In Excel, it is mandatory to maintain a **valid excel date format** for making a project, data handling, and data analysis as well. Thus we must arrange the invalid Excel date format to a **valid Excel date format** such as ‘DD-MMM-YY’ (or any other valid date format) suggested by Excel.

But now the question arises in mind how to change the date format in Excel?

**I. HOW TO CHANGE DATE FORMAT IN EXCEL?**

We can change the **Excel date format** in 5 ways:

**(01) CHANGE EXCEL DATE FORMAT: USING EXCEL SHORTCUT CTRL+SHIFT+#**

Select the date range B2:B14 and then press **Ctrl+Shit+#**. All are arranged in a valid date format.

**(02) CHANGE EXCEL DATE FORMAT: BY THE ‘FORMAT CELLS’ DIALOG BOX (USING EXCEL SHORTCUT CTRL+1)**

Alternatively, select the data range B2:B14 ➪ then press ‘**Ctrl+1**‘ which will open the **‘Format Cells’** dialog box ➪ go to the ‘Custom’ option under the Number tab ➪ On the right side, mentioned the required date format like dd-mmm-yy under the **‘Type**‘ section.

**(03) CHANGE EXCEL DATE FORMAT: ****BY ****THE ‘FORMAT CELLS’ DIALOG BOX (USING THE RIBBON)**

Alternatively, select the data range B2:B14 ➪ Go to the Home tab ➪ Click ‘**dialog box launcher’** located on the right side corner of the ‘Alignment’ group or ‘Number’ group ➪ the **Format Cells** dialog box opens ➪ Select the **‘Custom’** option under the Number tab ➪ On the right side, write the valid date format like dd-mmm-yy under the **‘Type’ **section.

Finally, click **OK** or press **Enter** on the keyboard. We get the result in the desired date format as shown below:

**(04) CHANGE EXCEL DATE FORMAT: USING THE ‘CONVERT TEXT TO COLUMNS’ WIZARD**

The Convert Text to Columns Wizard is an advanced tool in Excel that helps to convert a range of varying date formats (such as text, number, invalid date format, valid date format etc.) to valid date formats.

➢ First, select the range of cells, i.e., A2:A14 **➪ **then press** Alt+D+E** (sequentially **Alt, D, E**) or **Alt+A+E** (sequentially **Alt, A, E**) which will open the **Convert Text to Column**s Wizard.

• In Step 1 of 3, select ‘Delimited’ (Excel by default select this option) ➪ then press ‘Enter’ or click ‘Next’.

• In Step 2 of 3, uncheck all the checkboxes, if any (or, we can ignore the Tab checkbox) ➪ then press ‘Enter’ or click ‘Next’.

• In Step 3 of 3, select the ‘Date’ option ➪ finally press ‘Enter’ or click ‘Finish’.

➢ After applying the Text to columns, select the range again **➪ **Then** **press **Ctrl+Shift+#** which allows us to convert numbers to valid Excel date format quickly.

**(05) CHANGE EXCEL DATE FORMAT: USING THE ‘CUSTOM FORMATTING’**

Excel gives us a great deal of flexibility in creating custom date formats. When we create a custom date format, it can be used to format any cells in the workbook. We can create as many custom date formats as we need with the help of the codes used to create custom formats for dates and times.

HOW TO APPLY EXCEL CUSTOM DATE FORMAT?

We can apply the Excel custom date format with the ‘Custom’ category in the ‘Format Cells’ dialog box.

There are 06 ways to launch the Format Cells Dialog box, as explained below:

(01) Select the cell(s) ➪ Press **Ctrl+1** on the keyboard which will open the ‘**Format Cells**’ dialog box ➪ Click the ‘**Custom**’ category on the ‘Number’ tab ➪Type the desired formatting code in the ‘__T__ype:’ box or select the predefined formatting code from the below list.

(02) **Right-click** the selected cell(s) and then select **Format Cells** which will open the ‘**Format Cells**’ dialog box ➪ Click the ‘**Custom**’ category on the ‘Number’ tab.

(03) Go to the Home tab ➪ Click the **Number dialog box launcher, **a small downward diagonal arrow, located at the bottom right corner of the ** Number group** which will open the ‘

**Format Cells**’ dialog box ➪ Click the ‘

**Custom**’ category on the ‘Number’ tab.

(04) Go to the Home tab ➪Click the Number Format drop-down in the Number group ➪ then click **‘More Number Formats’** located at the bottom of the list, which will open the ‘**Format Cells**’ dialog box ➪ Click the ‘**Custom**’ category on the ‘Number’ tab.

(05) Go to the Home tab ➪ Click the **Font dialog box launcher **located at the bottom right corner of the ** Font group** ➪ ‘Font’ tab in the Format Cells dialog box opens then move to the ‘Number’ tab by clicking on it ➪ Click the ‘

**Custom**’ category on the ‘Number’ tab.

(06) Go to the Home tab ➪ Click the **command dialog box launcher **located at the bottom right corner of the ** Alignment group** ➪ ‘Alignment’ tab in the Format Cells dialog box opens then move to the ‘Number’ tab by clicking on it.

# II. HOW TO EXCEL CONVERT DATE TO TEXT?

Sometimes it is required to convert a valid date format to a text format in Excel. We can follow any of the following methods:

## (01) EXCEL CONVERT DATE TO TEXT: USING THE ‘CONVERT TEXT TO COLUMNS’ WIZARD

Steps to Start:

➢ At first, select the range of cells, i.e., A2:A14 ➪ then press Alt+D+E (sequentially Alt, D, E) or Alt+A+E (sequentially Alt, A, E) which will open the ‘Convert Text to Columns Wizard’.

• In Step 1 of 3, select ‘Delimited’ (Excel by default select this option) ➪ then press ‘Enter’ or click ‘Next’.

• In Step 2 of 3, uncheck all the checkboxes, if any (or, we can ignore the Tab checkbox) ➪ then press ‘Enter’ or click ‘Next’.

• In Step 3 of 3, select the ‘Text’ option ➪ finally press ‘Enter’ or click ‘Finish’.

➢ As a result, the date format is converted to a text format, but without changing the *date format*.

## (02) EXCEL CONVERT DATE TO TEXT: WITH THE ‘FORMAT CELLS’ DIALOG BOX (CTRL+1)

➢ At first, select the range of cells, i.e., A2:A14 ➪ then use Excel shortcut Ctrl+1 which will open the ‘Format Cells’ dialog box.

➢ In the second step, click on the ‘Number’ tab ➪ then select the ‘Text’ category ➪ finally press ‘Enter’ or click ‘OK’.

➢ As a result, the date format is converted to a text format where the *date format* changes to the *general format*.

## (03) EXCEL CONVERT DATE TO TEXT: WITH THE ‘FORMAT BOX’ ON THE RIBBON (ALT+H+N)

➢ Method1: select the range of cells, i.e., A2:A14 ➪ then press Alt+H+N (sequentially press Alt, H, N) and type ‘te’ which will select the ‘Text’ category in the format box of the ribbon ➪ then press ‘Enter’ or ‘OK’.

Alternatively,

➢ Method2: select the range of cells, i.e., A2:A14 ➪ then press Alt+H+N (sequentially press Alt, H, N) which will select the format box of the ribbon ➪ then press Alt+⬇ (down arrow) which allows us to open a drop-down menu ➪ select the ‘Text’ category ➪ press ‘Enter’ or ‘OK’.

➢ As a result, the date format is converted to a text format where the *date format* changes to the *general format*.

## (04) EXCEL CONVERT DATE TO TEXT: USING THE TEXT FORMULA

We can use the Excel TEXT function to convert a valid date format or numeric value to a text string.

The Syntax for the TEXT Function:

Arguments:

➢ value: It can be a numeric value, valid date format, a formula that returns a numeric value, or a cell reference contains a number. Value is the argument we want to convert to text.

➢ format_text: This argument defines how to format the input value argument as a text. Remember that the mentioned text format should be enclosed in double quotation marks.

Using a set of date codes, we can format a date any way we like.

Note: We can separate the days, months, and year in date codes with various delimiters such as dash (-), slash (/), comma (,) colon (:), etc.

Example:

## (05) EXCEL CONVERT CURRENT DATE TO TEXT: USING THE TEXT FORMULA

We can easily convert the current date to the text format using the TEXT function. In this case, we are using the TODAY function within the TEXT function. TODAY() function returns the current date. For example: **=TEXT(TODAY(),”dd-mmm-yy”)** and the formula returns the result as **03-Dec-20**.

However, we can use the different format codes as per requirement.

# III. HOW TO EXCEL CONVERT TEXT TO NUMBER?

# Or, HOW TO CONVERT TEXT TO DATE IN EXCEL?

## (01) EXCEL CONVERT TEXT TO NUMBER: USING THE TEXT FORMULA

We can also use the Excel **TEXT formula** to convert text to number by adding double negation (–) prior to the TEXT formula and after the equality (=) sign.

For example, **=–TEXT(A2,”dd-mmm-yy”)**

After converting the number, press **Ctrl+Shift+#**which converts the number to date.

## (02) EXCEL CONVERT TEXT TO NUMBER: USING THE INT FUNCTION

The INT function rounds a number down to the nearest integer, based on the value of the fractional part of the number.

The Syntax for the INT function:

**➢ number: **[**Required**] The number we want to round.

In the given example, the **INT function** is applied in cell C2.

=INT(A2)

The formula returns the result 43587.

Then copy the formula till the end of the range.

Finally, convert the formula into values with the help of Paste Special dialog box.

## (03) EXCEL CONVERT TEXT TO NUMBER: USING THE TRUNC FUNCTION

Excel **TRUNC function** truncates a number to a specified number of significant digits. The TRUNC function simply removes the fractional part of a number.

The Syntax for the TRUNC function:

**➢ number: **[**Required**] The number we want to round or truncate.

**➢ num_digits:****[optional]** This is a number that specifies the number of digits we want to truncate. If kept it blanks, it will take 0 as the default value.

num_digits Description:

**> 0** (**A positive value that is greater than zero**): It specifies the number of digits truncates to the right of the decimal places.

**0** (**Equals to zero**): It specifies the truncates all decimal places (this is the default)

**< 0** (**A negative value that is less than zero**): It specifies the number of digits truncates to the left of the decimal places.

In the given example, the TRUNC function is applied in cell C2.

=TRUNC(A2)

The formula returns the result 43587.

Then copy the formula till the end of the range.

Finally, convert the formula into values with the help of Paste Special dialog box.

## (04) EXCEL CONVERT TEXT TO NUMBER: USING THE EXCEL DATE FORMULA

The Excel Date formula is used to build a custom date by providing numbers or text string based on the year, month, and day provided.

A date consists of three components: the year, month, and day.

**The Syntax for the Excel DATE Formula:**

**➢ year:** This argument can be from one to four digits. Microsoft Excel for Windows uses the 1900 date system (a number between 1900 and 9999)

**➢ month:** A number representing the month of the year (1 to 12).

**➢ day:** A number representing the day of the month (1 to 31).

**Note:** If we enter a two-digit year, or even a three-digit year, Excel converts the number into a year value by adding 1900. Therefore, while we entering 10 as the *year* argument it gives 1910, not 2010. To avoid problems, always use a four-digit year when entering the DATE() function’s year argument.

**Example 1:**

Sometimes dataset contains dates coded as text strings. For example, **20190502** – the text represents the date 02-May-2019 and the text is arranged in reversed order 2019-05-02 (a four-digit year followed by a two-digit month, followed by a two-digit day).

To convert this string to an actual date with the **EXCEL DATE formula** nested with text functions (LEFT, MID, and RIGHT).

**• The LEFT() Function:**

Returns the specified number of characters from the start of a text string.

The Syntax for the LEFT function:

**➢**** text:** The original text or reference to a cell that contains the characters to be extracted.

**➢ num_chars:** The number of characters we want to extract from the left. The default value is 1.

For example, =LEFT(“**2019**0502”, 4)

The formula returns **2019** (i.e., four characters from the left of a string).

**• The MID() Function**

Returns the specified number of characters from the middle of a text string, based on a starting position and number of characters.

The Syntax for the RIGHT function:

**➢**** text:** The original text or reference to a cell that contains the characters to be extracted.

**➢ start_num:** The character position in a string at which we want to start extracting the characters.

**➢ num_chars:** The number of characters we want to extract

For example, =MID(“2019**05**02”, 5, 2)

The formula returns **05** (i.e., two characters from the middle of a string).

**• The RIGHT() Function**

Returns the specified number of characters from the end of a text string.

The Syntax for the RIGHT function:

**➢ text:** The original text or reference to a cell that contains the characters to be extracted.

**➢ num_chars:** The number of characters we want to extract from the right. The default value is 1.

For example, =RIGHT(“201905**02**”, 2)

The formula returns **02** (i.e., two characters from the right of a string).

Now, combine the formula in cell C2:

**=DATE**(**LEFT(A2,4)**,**MID(A2,5,2),****RIGHT(A2,2)**)

**➢ LEFT(A2,4) returns the Year 2019.**

**➢ MID(A2,5,2) returns the Month 05 (i.e. May).**

**➢ RIGHT(A2,2) returns the Day 02**

After applying the formula press **Ctrl+Enter** or **Enter** key to apply the formula.

Then copy the formula till the end of the range.

Finally, convert the formula into values with the help of the Paste Special dialog box.

**Example 2:**

Sometimes, the date text format may be different (either use “D/MM/YYYY, “DD/MM/YYYY” or both can be used in a range).

**Step 1:** In this scenario, first we convert the range in **“DD/MM/YYYY”** format with the help of the TEXT function. For example,

**=TEXT(A2,”00000000″)**

**Step 2: **In the second step, combine the TEXT functions (**LEFT, MID, and RIGHT**) in the **DATE function** to make a nested formula.

For example,

=DATE(RIGHT(C2,4),MID(C2,3,2),LEFT(C2,2))

**➢ RIGHT(C2,4) returns the Year 2019.**

**➢ MID(C2,3,2) returns the Month 05 (i.e. May).**

**➢ LEFT(C2,2) returns the Day 02**

After applying the formula press **Ctrl+Enter** or **Enter** key to apply the formula.

**Step 3: **Then copy the formula till the end of the range.

**Step 4: **Finally, convert the formula into values with the help of the Paste Special dialog box.

## (05) EXCEL CONVERT TEXT TO NUMBER: USING THE DATEVALUE FUNCTION

**DATEVALUE() function** converts a date in the form of text to a serial number. This formula ignores the time part convert to a serial number which means it only converts the *day part* of a date to a serial number excluding the *time part*.

**The syntax for the DATEVALUE function:**

**➢ date_text: **The string or cell reference containing the date.

The following formula returns 43587, the date serial number for 02.05.2019 2:43:00 PM. This formula simply *ignores time* to include in the result.

**=DATEVALUE(“02.05.2019 2:43:00 PM”)**

Using cell reference:

**=DATEVALUE(A2)**

After applying the formula in a cell, extends it to the entire range.

To view the result of this formula as a date, we need to apply a date number format to the cell. Generally, we select the cell or range of cells and press the keyboard shortcut **Ctrl+Shift+#**.

## (06) EXCEL CONVERT TEXT TO NUMBER: USING THE SUBSTITUTE FUNCTION

In some situations, we may need the formula to replace a part of a text string with some other text. In this case, we use the Excel SUBSTITUTE function. The SUBSTITUTE function substitutes or replaces old text with new text in a text string.

The syntax for the SUBSTITUTE function:

**➢ text:** The original text or reference to a cell that contains the characters to be substituted.

**➢ old_text: **the text to be replaced

**➢ new_text:** the text we want to replace with old_text.

**➢ instance_num:** It specifies which occurrence of old_text we want to replace with new_text. When instance_num is specified, only that instance is changed. If we do not include instance_num, all occurrences are changed.

In the given example, we may need to convert the **full stop (.)** character to some other character likes **hyphen (-)** with the SUBSTITUTE function.

We wrap the SUBSTITUTE function either with the **VALUE function** or the **DATEVALUE function**.

**• DATEVALUE() function **converts a date in the form of text to a serial number where it only converts the ‘*day’ part* in date to a serial number except the ‘*time’ part*.

**The syntax for the DATEVALUE function:**

**➢ date_text:** The original text or reference to a cell that contains the date text.

**=DATEVALUE(“02.05.2019 2:43:00 PM”)**

The formula returns the result:43587

When we wrap the SUBSTITUTE function and use the cell reference, it looks like:

**=DATEVALUE(SUBSTITUTE(A6,”.”,”-“))**

This formula signifies that the SUBSTITUTE function replaces full stop ”.” from cell content in cell A6 with a hyphen “-“. Then DATEVALUE function converts a date text string (the only daypart) into a date serial number and returns the result 43587.

To view the result of this formula as a date, we need to apply a date number format to the cell. Generally, we select the cell or range of cells and press the keyboard shortcut **Ctrl+Shift+#**.

• **VALUE() function** converts a date in the form of text to a numeric number. This formula includes the time into the numeric number as decimal places.

**The syntax for the VALUE function:**

**➢ text:** The original text or reference to a cell that contains the characters.

The following formula returns 43587.613, the date serial number for 02.05.2019 2:43:00 PM. This formula *allows time* to include in the result.

**=VALUE(“02.05.2019 2:43:00 PM”)**

When we wrap the SUBSTITUTE function and use the cell reference, it looks like:

**=VALUE(SUBSTITUTE(A6,”.”,”-“))**

This formula suggests that the SUBSTITUTE function replaces full stop ”.” from cell content in cell A6 with a hyphen “-“. Then the VALUE function converts a date text string (both day and time parts) into a numeric number and returns the result 43587.613.

Finally, convert this number to a date format (DD-MMM-YY) with the help of the keyboard shortcut **Ctrl+Shift+#**.

**Note:** In the case of a date where both day and time measured, we always prefer to apply the DATEVALUE() function instead of the VALUE() function to avoid the time part.

## (07) EXCEL CONVERT TEXT TO NUMBER: USING THE CONCATENATE FUNCTION

**CONCATENATE function** is a text function that joins between 2 and 255 individual text strings into one text string.

➢ In the given example, we use DAY(), MONTH(), YEAR() functions and joined them with CONCATENATE() function. All the functions make a nested formula and return a text date format.

=CONCATENATE(DAY(A2),”/”,MONTH(A2),”/”,YEAR(A2))

➢ Extend the formula till the end of the range (till cell C14).

➢ Convert the formula into Values

Method 1: Select and copy the range (C2:C14) ➪ press **Alt+E+S+V** (sequentially press **Alt, E, S, V**) or press **Alt+Ctrl+V+V **(**Alt+Ctrl+V**, then **V**) which will select the *‘ Values‘* option in the ‘

**Paste Special**‘ dialog box ➪ Press

**Enter**or click

**Ok**.

Method 2: Equivalently, Select and copy the range (C2:C14) ➪ press **Alt+E+S+U** (sequentially press **Alt, E, S, U**) or press **Alt+Ctrl+V+U **(**Alt+Ctrl+V**, then** U**) which will select the **‘ Values and number formats‘** option in the

**‘Paste Special’**dialog box ➪ Press

**Enter**or click

**Ok**.

➢ Convert Text to Date Format (Using Text to Columns)

This is a crucial step to convert text to date.

Select the range and press **Alt+D+E** (sequentially press **Alt, D, E**) or **Alt+A+E** (sequentially press **Alt, A, E**) which will open the ‘* Convert Text to Columns Wizard*‘.

• In Step 1 of 3, select ‘Delimited’ (Excel by default select this option) ➪ then press ‘Enter’ or click ‘Next’.

• In Step 2 of 3, uncheck all the checkboxes, if any (or, we can ignore the Tab checkbox) ➪ then press ‘Enter’ or click ‘Next’.

• In Step 3 of 3, Excel by default selects the ‘**General**‘ option ➪ Then press ‘Enter’ or click ‘Next’.

Equivalently, selects the ‘**Date**‘ option ➪ then press ‘Enter’ or click ‘Next’.

➢ As a result, the text date format is converted to a valid date format.

## (08) EXCEL CONVERT TEXT TO NUMBER: USING THE DAY, MONTH, YEAR COMBINED FUNCTION

We can create a valid DATE format by joining **DAY(), MONTH(),** and **YEAR()** functions with the **ampersand (&)** symbol. Additionally, use a separator within double quotations (such as hyphen “-“, slash “/”) in between them.

=DAY(A2)&”/”&MONTH(A2)&”/”&YEAR(A2)

➢ Extend the formula till the end of the range (till cell C14).

➢ Convert the formula into Values.

➢ Convert Text to Date Format (Using Text to Columns)

## (09) EXCEL CONVERT TEXT TO NUMBER: USING THE TEXT TO COLUMNS WIZARD

In the given example, first, we remove time from the date and simultaneously convert the text date to valid date format with the help of **‘Convert Text to Columns Wizard’**.

➢ Select the range and press **Alt+D+E** (sequentially press **Alt, D, E**) or **Alt+A+E** (sequentially press **Alt, A, E**) which will open the ‘* Convert Text to Columns Wizard*‘.

• In Step 2 of 3, select the ‘**Space’** checkbox as a delimiter ➪ Then press ‘Enter’ or click ‘Next’.

As a result, the date and time slot will be separated as display in the * Data Preview* section.

• In Step 3 of 3, select the ‘**Date’** radio-button, the Excel by default select **‘DMY’** format. If we want to select another format, then click on the drop-down and choose a desired format from the drop-down list ➪ Then press ‘Enter’ or click ‘Finish’.

As a result, date and time slots are split into different columns and additionally, text date format converted to valid date format.

If you would like to improve your academic and professional career as well, then the below courses help you a lot. Instead of Advanced Excel, a number of best courses suggested you through this platform that boosts your confidence and flies your career high. Advance Excel Forum

## Premium Courses on ed2go

**Advanced Microsoft Excel 2019/Office 365**

**Microsoft Excel 2019 Certification Training (Voucher Included)**

**Accounts Payable Specialist Certification with Microsoft Excel 2019 (Voucher Included)**

**Microsoft Office Specialist 2019 (MOS) Certification Training (Vouchers Included)**

**Microsoft Office Specialist 2013**

**Certified Administrative Professional with Microsoft Office Specialist 2019 (Vouchers Included)**

**Certified Internal Auditor with Microsoft Excel 2019**

**Executive Assistant with Microsoft Office Specialist 2019 (Vouchers Included)**

**Payroll Manager**

Bestseller ********** 4.9/5

## Premium Courses on Coursera

Excel Skills for Business Bestseller ********** 4.9/5 Excel Skills for Business: Essentials Bestseller ********** 4.9/5 Introduction to Data Analysis Using Excel Bestseller ********** 4.9/5

**Artificial Intelligence Data Fairness and Bias**

**Data Visualization & Dashboarding with R**

**Managing, Describing, and Analyzing Data**

**Understanding and Visualizing Data with Python**

**Advanced Data Science with IBM**

**Predictive Analytics and Data Mining**

Bestseller ********** 4.9/5

## Premium Courses on Udemy

Mastering Excel 2019 – Intermediate Top Rated ********** 4.8/5 Microsoft Excel Data Analysis and Dashboard Reporting Bestseller ********** 4.7/5 Microsoft Excel Pivot Tables – Master Excel Pivot Tables! Top Rated ********** 4.7/5

**Microsoft Excel Pivot Tables – Master Excel Pivot Tables!**

**77-728 Microsoft Excel 2016 Expert Certification**

**Ultimate Microsoft Excel Course: Beginner to Excel Expert**

**Learn python**

**Google Professional Cloud Data Engineer**

**Data Analysis with Machine Learning & Data Visualization**

Top Rated ********** 5/5

**30+ BEST ADVANCE EXCEL COURSES | By Coursera, Udemy |**

**36+ BEST ADVANCED EXCEL COURSE ONLINE | By ed2go |**

**80+ Excel Shortcuts with ALT Key || Best Hotkey of Keyboard Shortcuts**

**10 Examples of Text to Columns || How to Split Cells/Columns in Excel**

**04 Best Ways: How to Transpose Data in Excel**

**How to use VLOOKUP Function in Excel || Must know Do OR Dont’s ||**

**03 Best Ways: Excel REVERSE VLOOKUP | VLOOKUP to the left |**

**03 Best Ways: EXCEL DOUBLE VLOOKUP/ IFERROR VLOOKUP/ NESTED VLOOKUP**