How do you format date from ddmmyyyy (10/6/2025 12:36:26 PM) to yyyymmdd (2010/6/25)?

[svjonson]

Hi guys,

Hope you can help me out on formatting the date.

CURRENTLY READ AS:
10/6/2025 12:36 (DD/M/YYYY)

BUT SHOULD BE READ AS:
2010/06/25 (YYYY/MM/DD)

PS. I have tried Format>Number>Custom> yyymmdd but this happens:
2025/10/06 12:36
The year just changes position.

PS. There are more than a thousand entries with this wrong date format that needs to be changed.

[thinkspac]

The numbers were entered correctly into the wrong format, if that makes sense. You will have to re-enter the information into the correct format.

Set the Format as > yyyy/mm/dd > select the cell and type "6/25/2010"

[mth]

Hi all,

Strange format, but may be with a formula ?

Please Login or Register  to view this content.

and format dd/mm/yyyy

Regards,

m

[svjonson]

The numbers were entered correctly into the wrong format, if that makes sense. You will have to re-enter the information into the correct format.

Set the Format as > yyyy/mm/dd > select the cell and type "6/25/2010"

Thanks for the quick response. But if I need to re-enter the info into the correct format, I have thousands of entries that needs to be changed. This data was exported from html and the format in html is 10-06-25 12:36 but when I exported it in excel - 10/6/2025 12:36 when it should be formatted as 2010/06/25 12:36. Hope this will be fixed without programming - i don't know how to! TIA

[Kevin UK]

Hi svjonson

Attached file I have used a helper column. See if that will be of any assistance!

[svjonson]

Hi svjonson

Attached file I have used a helper column. See if that will be of any assistance!

Thanks for the assistance. I've tried it and there's an error VALUE! Please check screenshots.
Let me know the fix. TIACapture.JPGCapture1.JPG

[shg]

You're likely to get you problem solved sooner if you post workbooks rather than pictures.

[Kevin UK]

Hi svjonson

All I can say, the dates you have are incorrect.
In your first post you stated:

CURRENTLY READ AS:
10/6/2025 12:36 (DD/M/YYYY)

BUT SHOULD BE READ AS:
2010/06/25 (YYYY/MM/DD)

In your attachment in Post: #6

You have entered 10/8/2040. There is not 40th day in a month!

[thameem127]

Hai svjonson,

Keep importing data from Html to any column for example column A1
then type formula like this in column B1

Please Login or Register  to view this content.

[Kevin UK]

Hi thameem127

That is not what the OP requires. You need to read Post #1
In my attachment the TEXT function is being used in the helper column. Then the data is arranged!