56

I have been searching for about an hour on how to do this in Excel.

I have an Excel file that was created from an old system and I am pulling information from a SQL Server Database, I will be inputting the information back into the SQL Server Database and would like the Dates to match.

I have tried Creating a Custom Format, but I am unsure if I even did it Correctly. I found several places where they want to go the other way mm/dd/yyyy to yyyymmdd but they have not been helpful.

I am unfamiliar with using VBA in any Microsoft Products otherwise I am sure that this would be a simple Task.

I have two separate columns that need to be changed.

How do I Format the entire column from (float)yyyymmdd to a (Date)mm/dd/yyyy

Community
  • 1
  • 1
Malachi
  • 3,205
  • 4
  • 29
  • 46
  • 2
    Right Click on the row and click on Format cells. Under the Number tab, click on Custom. There you can set it to `mm/dd/yyyy` – Siddharth Rout Oct 24 '12 at 19:11
  • 6
    I tried that and all it did was insert `#################....` – Malachi Oct 24 '12 at 19:12
  • Did you increase the width of the column? – Siddharth Rout Oct 24 '12 at 19:13
  • what happens when you hover your mouse over the cell? Does it show any message? – Siddharth Rout Oct 24 '12 at 19:16
  • `Dates and times that are negative or too large display as #######.` – Malachi Oct 24 '12 at 19:17
  • 1
    Ok In that case I need to see your Excel file. Can you upload few rows of sample data in www.wikisend.com and share the link here? – Siddharth Rout Oct 24 '12 at 19:19
  • 3
    @SiddharthRout When Malachi attempts to display the number as a date, Excel tries to make it a date... which baesd on the number provided would probably be something 50-60 thousand years in the future. I think that counts as too large. – Daniel Oct 24 '12 at 19:29
  • 2
    As an aside, apparently the largest date that can be displayed in a cell as a date (Excel 2010) is 2958465 which is 12/31/9999 – Daniel Oct 24 '12 at 19:38
  • @Daniel wonder if Excel will still be in use by then? Y10K bug? – Malachi Apr 21 '17 at 12:43
  • 1
    @Malachi I haven't wondered about it before. Now I won't lose sleep over it. I suspect we won't be around to find out. – Daniel Apr 21 '17 at 14:27

5 Answers5

74

You can convert the value to a date using a formula like this, next to the cell:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Where A1 is the field you need to convert.

Alternatively, you could use this code in VBA:

Sub ConvertYYYYMMDDToDate()
   Dim c As Range
   For Each c In Selection.Cells
       c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2))
       'Following line added only to enforce the format.
       c.NumberFormat = "mm/dd/yyyy"
   Next
End Sub

Just highlight any cells you want fixed and run the code.

Note as RJohnson mentioned in the comments, this code will error if one of your selected cells is empty. You can add a condition on c.value to skip the update if it is blank.

Daniel
  • 12,982
  • 3
  • 36
  • 60
  • 3
    Sweet! Here's one for YYYYMMDDHHMMSS to Date: `=DATE(LEFT(B1,4),MID(B1,5,2),MID(B1,7,2))` – emragins Oct 26 '14 at 02:25
  • Would you mind running this for YYYYMMDD to DD/MM/YYYY ? – Gaia Jul 09 '15 at 05:02
  • @Gaia For that, you'd need to update the numberformat to "dd/mm/yyyy". I.E. `c.NumberFormat = "dd/mm/yyyy"` – Daniel Jul 09 '15 at 12:48
  • So it is not possible in using the formula, @DanielCook ? – Gaia Jul 09 '15 at 15:55
  • 1
    @Gaia. Sort of. There are only two ways to make the date display differently. You must either change the formatting of the cell manually (which is what the code option does), or update the default date format in your operating system. – Daniel Jul 09 '15 at 18:03
  • If someone struggles with this format (MM/DD/AAAA) here the answer to convert it to AAAA/MM/DD: =DATE(MID(D2;7;4);LEFT(D2;2);MID(D2;4;2)) – David Létourneau Feb 20 '17 at 21:13
  • Had a bit of an issue with blank spaces, added an If statement to the above solution: Sub ConvertYYYYMMDDToDate() Dim c As Range For Each c In Selection.Cells If c.Value <> "" Then c.Value = DateSerial(Left(c.Value, 4), Mid(c.Value, 5, 2), Right(c.Value, 2)) 'Following line added only to enforce the format. c.NumberFormat = "mm/dd/yyyy" End If Next End Sub – RJohnson Nov 20 '18 at 19:51
  • 1
    @RJohnson True enough, I added a note about empty cells. – Daniel Nov 20 '18 at 20:02
27

Do you have ROWS of data (horizontal) as you stated or COLUMNS (vertical)?

If it's the latter you can use "Text to columns" functionality to convert a whole column "in situ" - to do that:

Select column > Data > Text to columns > Next > Next > Choose "Date" under "column data format" and "YMD" from dropdown > Finish

....otherwise you can convert with a formula by using

=TEXT(A1,"0000-00-00")+0

and format in required date format

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • your Answer is also Correct! i was actually looking for what @Leo and @DanielCook Answered. but the `Text to columns` is the Best Solution to the Problem that I encountered. probably not as fast as writing one formula and Copy Paste it to the other cells, or running a Macro. but a very good Solution! and I hope that everyone Upvotes this answer as well! – Malachi Oct 24 '12 at 19:43
  • This seems to be actual answer, since we do not need any formulas over here and the entire column can be converted with this. Thanks for providing this answer – sunil Apr 08 '15 at 07:58
  • @sunil from what I can remember I actually tried this and it changed the data in the column incorrectly and that is the reason why I didn't do it this way. I accepted the answer that actually provided me with a solution to my problem, please give that answer the upvotes that it deserves. – Malachi Aug 24 '15 at 16:26
  • This answer is actually wrong, I wanted to go ***from*** `YYYYMMDD` to a date format recognized by Excel. not the other way around. – Malachi Aug 24 '15 at 16:27
  • TEXT function will simply convert data like 20180130 to a text value 2018-01-30. If you want a real date then +0 will make that conversion so you can then format the date any way you want. Any operation which doesn't change the value will work, e.g *1 – barry houdini Jan 30 '18 at 08:18
6

Here is a bare bones version:

Let's say that you have a date in Cell A1 in the format you described. For example: 19760210.

Then this formula will give you the date you want:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)).

On my system (Excel 2010) it works with strings or floats.

Graham Borland
  • 60,055
  • 21
  • 138
  • 179
Leo
  • 2,775
  • 27
  • 29
6

for converting dd/mm/yyyy to mm/dd/yyyy

=DATE(RIGHT(a1,4),MID(a1,4,2),LEFT(a1,2))
Malachi
  • 3,205
  • 4
  • 29
  • 46
user2027618
  • 61
  • 1
  • 1
  • this isn't quite was I was asking for, but I can see how it would be useful for someone else, so I up-voted it! – Malachi Jan 31 '13 at 14:25
3

Found another (manual) answer which worked well for me

  1. Select the column.
  2. Choose Data tab
  3. Text to Columns - opens new box
  4. (choose Delimited), Next
  5. (uncheck all boxes, use "none" for text qualifier), Next
  6. use the ymd option from the Date dropdown.
  7. Click Finish
rwb
  • 4,309
  • 8
  • 36
  • 59