How do I convert a text date in excel from dd.mm.yy to dd/mm/yyyy?

How do I convert a text date in excel from dd.mm.yy to dd/mm/yyyy? and have the new dd/mm/yyyy cell an actual date cell?

5 Answers

• Suppose in cell A2 you have written a date like, 13.12.2017 and it's format is Text.

So to convert into dd/mm/yyyy, you can use the following.

=TEXT(DATEVALUE(RIGHT(A2,4)&"/"&MID(A2,4,2)&"/"&(LEFT(A2,2))),"dd/mm/yyyy")

You get 13/12/2017.

• If A2 has 13.12.20, then you should have to use this formula.

=LEFT(A2,2)&"/"&MID(A2,4,2)&"/"&RIGHT(A2,2)

You get 13/12/20.

Use find and replace

Ctrl+F

select the replace tab

In the find what tab put . In the Replace with put /

click Options>

Change the format on the find what to whatever the cells you are referencing are formatted as.

That should change the date cells to the excel date format, then you can change the format of those cells to dd/mm/yy format

Or try this:

G

Original 12.16.2017 (cell G1)

H

Output 12 Cell Contents =VALUE(LEFT(LEFT(G1,5),2))

I

Output 16 Cell Contents =VALUE(RIGHT(LEFT(G1,5),2))

J

Output 2017 Cell Contents =VALUE(RIGHT(G1,4))

K

Output 12/16/2017 Cell Contents =DATE(J1,H1,I1)

Please try this forumula:

=TEXT(DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)),"DD/MM/YYYY")

The sample as shown in the following picture:

Brett

There are many ways to change or convert data type in excel some of them i m describing below

1. If DATA TYPE of cell is DATE then Change the format of cell or column

According to refereed from website ablebits.com following link

Default date format in Excel

How to change date format in Excel

In Microsoft Excel, dates can be displayed in a variety of ways. When it comes to changing date format of a given cell or range of cells, the easiest way is to open the Format Cells dialog and choose one of the predefined formats.

1. Select the dates whose format your want to change, or empty cells where you want to insert dates.

2. Press Ctrl+1 to open the Format Cells dialog. Alternatively, you can right click the selected cells and choose Format Cells… from the context menu.

3. In the Format Cells window, switch to the Number tab, and select Date in the Category list. Under Type, pick a desired date format. Once you do this, the Sample box will display the format preview with the first date in your selected data.

4. If you are happy for the preview, click the OK button to save the format change and close the window.

5. If the date format is not changing in your Excel sheet, most likely your dates are formatted as text and you have to convert them to the date format first in step 2.

When you work with dates in Excel, the short and long date formats are retrieved from your Windows Regional settings. These default formats are marked with an asterisk (*) in the Format Cell dialog window:

or use formula

for further options details and formats changing see the link mentioned above of ablebits.com and find more details

2. If data type is not text Converting text to date

if you want to keep data type only in text format then

 =SUBSTITUTE(A2,".","/")

or

=TEXT(DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)),"DD/MM/YYYY")

if you want to change data type to date data type

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

then change the the format of of date data type of column cell as told in step 1 as per option or change regional seetings

there are many ways see how to convert for your reference and sharing i am sending many links for you to look and understand many other ways with photographs steps and explanations in details in following links

If any of answer link or solution helped or work then do tell me which worked for you in comments for my knowledge and research and if not then feel free to further clarifications and doubts and if worked then please dont forget to vote or accept the answer