I have a report that generates dates in a DD.MM.YYYY format and I need to convert them to a YYYY-MM-DD format. I've tried the custom option in format cells but nothing happens to change the format. Not sure what to try next...
Five answers:
Scrawny
2012-07-12 06:41:55 UTC
I managed to do this but it was surprisingly difficult. The format DD.MM.YYYY isn't a date format recognized by Excel. At least my Excel doesn't recognize it.
Use the substitute command to change the format of the date to a recognizable date format by Excel. I entered this in B1.
=SUBSTITUTE(A1,".","-") If the - is set up in your Regional Settings for Short Date format, you should simply be able to select the cell and enter the custom format of yyyy-mm-dd to change the date to the order that you want. That may not work and you may have to format B1 as a number then use another cell C1 formatted as yyyy-mm-dd then enter this formula into it. =B1
Unfortunately I couldn't find a "direct" way to use a formula and formatting to accomplish this.
IXL@XL
2012-07-12 06:28:36 UTC
Either make a custom format as described yyyy-mm-dd or use the Locale box and use a country that uses the format you want. By doing this you can enter dates in your regional format.
2012-07-12 06:28:18 UTC
Remember that it makes no difference what format the spreadsheet dates are in, Excel is smart enough to be able to sort on the date column from oldest to newest (or newest to oldest) in any format.
Once the format is saved for a column or row, when you type in dates in any format into those fields, they will be saved in the default date format you chose.
When this all doesn't seem to work the date has probably been saved as a text. Someone has entered or copied it, but it has never been recognized nor accepted by Excel as a date. Excel stubbornly refuses to apply another date format to a text like "mom's birthday", or "12.02.2009". While the first example is obvious, the second may confuse lots of people outside of the USA. Especially when their local version of Excel does accept it.
?
2016-10-24 04:08:19 UTC
once you're going to do something like this. you opt for something versatile and straightforward for your consumers to apply and that receives the interest performed. to objective this, all you truly opt for to do is reformat the cellular. This macro shall we them pick what cells they opt for to regulate the formatting on. If no cells are chosen, the macro exits. If cells are chosen then it alterations the format to ddmmyyyy. So i imagine this macro maximum heavily meets what your consumers are likely searching for. Sub DateChange() Dim MyRange As variety, x As variety Set MyRange = software.InputBox("pick the cells to regulate the date format.", _ "Date Changer", type:=8) If MyRange Is no longer something Then go out Sub for each x In MyRange x.NumberFormat = "ddmmyyyy" next x end Sub