1
1
My customer entered date in dd.mm.yyyy format. As I want insert all the data in mysql database, I want the date in yyyy-mm-dd format.
I tried to format using Excel custom date format and built-in function like =text(A1,"yyyy-mm-dd")
but it did not work, I still got the same format (it still shows dd.mm.yyyy).
For example, I typed 30.10.2010 in any cell, and tried changing the format to yyyy-mm-dd to be 2010-10-30, but it did not work.
I also looked for questions in Super User stack and Google but I could not find solution.
2Any problem with just setting the format on the cell? – a CVn – 2014-08-06T09:04:43.807
If I manually type the date, everything is OK. Format setting seems OK. – Mawia HL – 2014-08-06T09:06:10.773
1But what is the format? Excel will often auto-detect and set the format on the cell accordingly. Is the cell format text or date? If the latter, what happens if you simply change it to a date format more in line with what you want as output? – a CVn – 2014-08-06T09:07:07.180
The cell format is general, and I changed it to date, but it did not work. Just type 30.01.2010, and tried to change the format, but it is not working. So I wonder how we can change. – Mawia HL – 2014-08-06T09:09:04.993
1I tried, this works fine
=TEXT("30/01/2010", "yyyy-mm-dd")
- What does "not work" actually mean? Do you get an error message, does anything display, does it return nothing? – Dave – 2014-08-06T09:49:09.343=Text(A1,"yyyy-mm-dd"), does this work? And this is what I am asking. – Mawia HL – 2014-08-06T09:57:06.827
@DaveRook, thanks for your input. I also tried =Substitute(A1,".","-") and then tried to change the format from custom cell format, still nothing changes, There is no error message also. – Mawia HL – 2014-08-06T10:05:49.917
2You say nothing changes, but what do you see in the cell with
=Text(A1,"yyyy-mm-dd")
- is it empty? This works for me – Dave – 2014-08-06T10:15:01.2271No, it is not empty. Still the same format with the same value appears. For example, if A1 cell contains 30.10.2010, in cell B1 where I typed the formula, 30.10.2010 has come up again. I will update with the image. – Mawia HL – 2014-08-06T10:18:14.050
1When a date is initially entered in a form that matches the cell's date format it will be translated into a floating-point number -- days and fractions of a day. If you enter a format that doesn't match the cell's date format, or the cell doesn't have a date format set, then the data is stored as character data and will not auto-convert when you set/change the date format. – Daniel R Hicks – 2014-08-06T11:57:29.520