Even using text to columns I can't convert 4112025 to a date format, but other dates like 12012025 convert correctly.

Question

Using text to columns I was able to convert some of the dates in my spreadsheet to properly formatted dates. Others did not change at all. This effected only dates that have one digit for the month. Those dates are still formatted as "general." How can I fix this?

Answer 1

Dear @KNH,

Thank you for reaching out to Microsoft Q&A forum.

I understand that you're having an issue when converting dates in spreadsheets, especially when using the "Text to Columns" wizard.

The problem lies not in the "Text to Columns" function itself, but in how your version of Excel is interpreting the regional date format specifically, whether it reads the date as Month/Day/Year (M/D/Y) or Day/Month/Year (D/M/Y).

When you try to convert a single-digit month like '4' (April), the program gets confused because the structure of the data 4112025 is ambiguous.

You can specify the exact format of the incoming data within the Text to Columns wizard to force it to recognize the M/D/Y structure.

1. Select the entire column containing the date numbers (e.g., 4112025).
2. Go to the Data tab and click Text to Columns.
3. In Step 1 of 3, choose Fixed Width (or Delimited, depending on your data, but Fixed Width is often more robust for this). Click Next.
4. In Step 2 of 3, click Next (do not separate the numbers).
5. Crucial Step: In Step 3 of 3, under the "Column data format" section, select Date.
6. From the dropdown menu to the right of "Date," select the format that matches your data: MDY (Month, Day, Year).
7. Click Finish.

By explicitly telling the wizard that the numbers are in MDY format, it should correctly parse the single-digit months (4/11/2025) and two-digit months.

I hope this information is helpful. Please follow these steps and let me know if it works for you. If not, we can work together to resolve this. Thank you for your patience and understanding. If you have any questions or need further assistance, please feel free to share them in the comments so I can continue to support you. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 2

Dear @KNH,
If the Text to Columns wizard still fails, this formula correctly handles the varying lengths (6, 7, or 8 digits) by calculating the length of the Day and Month based on the total string length.

Assuming your date number is in cell A1, use this formula in cell B1:
=DATE(RIGHT(A1, 4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5, 2))

Breakdown of the Formula

Year: RIGHT(A1, 4):  Always extracts the last four digits (the Year).

Month: LEFT(A1, LEN(A1)-6): (Fixed) This is the key change.

• It calculates the total length of the string (e.g., 7 for 1021968).
• It subtracts 6 (4 for the year, 2 for the day) to determine the remaining digits, which must be the month (7 - 6 = 1 digit for the month, 8 - 6 = 2 digits for the month).

Day: MID(A1, LEN(A1)-5, 2): 

This starts from the position immediately after the month and takes two digits for the day. (The day is always two digits wide in terms of position, even if one is a leading zero).

After applying the formula, copy cell B1 down the column, and then use Paste Special > Values to convert the formulas back to static date numbers. Then apply the date format you desire.
I hope this helps resolve the issue smoothly. If you have any further questions or need additional assistance, feel free to share, I’m happy to help.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 3

Hi,

Using Data > Text To Columns, ensure that the data type of entries in that column is Text. In cell B2, type =py and press tab. Then enter this formula in cell B2

=from datetime import datetime

datetime.strptime(xl("A2"), "%d%m%Y")

Hope this helps.

Answer 4

This effected only dates that have one digit for the month

I would add a leading zero, then convert, to avoid getting Oct.