Can I change a date with no format (20051111) to date format?

P

Peo Sjoblom

As long as you have 2 digits for months and days,
select the column, do data>text to columns, click next twice,
under column data format select date and then select YMD
assuming that is the order of the original. Then it will retrun a date based
on your regional setting so if in US you should get MDY
 
B

Bernard Liengme

Alternatively use =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
If needed you could insert a column for this, then use Copy followed by
Paste Special-Values to convert formulas to dates; now delete the original
column
best wishes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel How to change the "Short Date" format in Excel? 4
Need help formatting a sheet 0
Pivot table macro filter data between yesterday and today (date+time) 0
Text To Date Conversion 2
how can I format a cell to change on a specific date 2
Date and Number cell formats 4
Date in numeric format 1
Some date &/or Times will not format 1

Top