change 2017/2/3 to 2017/02/03

Question

I have a column in Excel which shows date but not in date format, it is in text format and the values for those dates that have one character for the month or day don't contain a zero before them.

E.g., we have 2017/1/3 or 2017/11/4, but I want to change them to 2017/01/03 // 2017/11/04.

How can i do this in a simple way without using date format ? Here is what I tried that is too long and complicated:

1. split date to year, month and days
2. if length of month < 2 , add 0 before month
3. if length of day < 2 , add 0 before day
4. built new date

Answer 1

All of the dates you use as examples are perfectly recognizable by Excel as dates. So if you want "real" dates that you can format as you wish and that Excel can directly use in math:

1) If you want to original data to remain and to have the formattable date data available, then just use the formula =A1*1 (if your date is in cell A1) wherever you wish to do the work (maybe several columns over, or on another page... whatever fits your scheme). And format as you wish ("yyyy/mm/dd" or "yyyy/dd/mm" — your examples do not disambiguate which, m-d or d-m, you have).

[I might add that though it does not seem of interest to you for this problem, that if you have text dates and simply want to use them in other formulas, while it would usually work to simply reference them (as in ...A1+7... to get a date a week later) in your formula, maybe there would be an odd, even unique case where you'd do that and stil lit would fail. In that case, simply do this multiplication so the example above would be ... A1*1+7...]

2) If you wish to replace the original data with "real" dates, you can reformat the range of dates from "text" to a custom format of "yyyy-mm-dd", then enter "1" in a cell somewhere, copy that cell, select the range of dates again, and Paste|Special|Multiply. This overcomes the fact that when you change from text to numbers, Excel never recomputes with that idea. For a single cell, pressing F2 after reformatting, then Enter (immediately, making no changes) will do the same thing without being burdensome, but if you have 14,293 of these dates...

3) You can also achieve 2)'s result with the "Text to Columns" feature. Just select the data and click "Text to Columns", then choose "Fixed Width" and place your width line marker at "10", then, finally, click "Date" as the column data format. Press "Finish" and you have a column of real dates ready for you to apply the desired format.

Honestly, for a one-time use, 3) seems simpler than 2) and it happens "in place" so it doesn't disturb your struccture. But you wouldn't want to do it every time someone adds a date, day in and day out over years. Mind you, "one time use" could occur over and over: say you empty your data and load a new set in each week. Then 3) wouldn't be a turkey. And its approach would be easier for making a little recorded macro for use each time.

Well, a little answer became a wee long, so...

Answer 2

Since your Date are in TEXT format so that I would like to suggest you an easiest method to convert them into Excel Date.

Do the following:

1. Type 0 in a cell and Copy, or you may also Copy any blank cell.
2. Select Date range.
3. Right click & from Poped up menu click the Paste Special then from the Operation options click Add & finish with OK.
4. Now Dates will look like Numbers i.e. 43322.
5. Since Dates are already selected so just Right Click & from the menu click Format Cell command.
6. Finally apply yyyy/mm/ddformat & finish with Ok.