0

I have an excel file in which the dob column contains entry of this type Thu Aug 04 00:00:00 BST 1983. I am trying to import the excel file in SQL-Server therefore I want to convert it to YYYY-MM-DD type. I tried the format cells option but it is not working. Any simple way of doing it or do I have to find regex for it?

Here is the sample file.

enter image description here

Community
  • 1
  • 1
Dr. Mian
  • 3,334
  • 10
  • 45
  • 69
  • See [Why Not Images of Code and Sample Data](http://tinyurl.com/kdxb7le). –  Dec 02 '15 at 11:13

2 Answers2

1

You can use Excel's DATE() function to do this. This takes year, month and day as values. There is some manipulation required to your date to get these values.

Please see below. 3rd row shows values: 30532 becomes 1983-08-04 when it is formatted (show formulae is on so you can see 2nd row)

enter image description here

G42
  • 9,791
  • 2
  • 19
  • 34
  • Thanks a lot. Is it not possible to do it in one formula (combine them) because there are a lot of columns with similar date type and it will be a bit tedious to split each of them in year, month and day column and then combine them. – Dr. Mian Dec 02 '15 at 10:58
  • It is; replace `B2`,`C2` and `D2` in cell `E2` with the equations in those cells. This becomes `=DATE(RIGHT(A2,4),MONTH(DATEVALUE(RIGHT(LEFT(A2,7),3)&"1")),RIGHT(LEFT(A2,10),2))` – G42 Dec 03 '15 at 09:54
1

In case your string in excel is Thu Aug 04 00:00:00 BST 1983 with the same format, then you can try this:

Excel Example

Then goto Custom format cells and select Date first and then select Custom and type YYYY-MM-DD to get the desired format.

Vimalraj Selvam
  • 2,155
  • 3
  • 23
  • 52
  • Just to add that the column B in above case must be date type to work it properly. – Dr. Mian Dec 02 '15 at 11:22
  • Do you know how can I replace #VALUE! with blank as formula output. I tried IFERROR(formula, 0) but it is not working – Dr. Mian Dec 02 '15 at 11:53
  • @Asbat: I guess you are asking about this: http://stackoverflow.com/questions/10689235/how-do-i-recognize-value-in-excel-spreadsheets – Vimalraj Selvam Dec 02 '15 at 11:56
  • Thanks. I used it wrong first I tried =IFERROR(Your_formula, 0) where as the correct one is =IFERROR(Your_formula, "") – Dr. Mian Dec 02 '15 at 12:00