Skip to main content
Support is Offline
Today is our off day. We are taking some rest and will come back stronger tomorrow
Official support hours
Monday To Friday
From 09:00 To 17:30
  Saturday, February 19, 2022
  10 Replies
  8.8K Visits
I am hoping someone can help me with my problem.
I have a xlsx file with the dates formatted in US (m/d/yyyy) style.
I would like to convert them to YYYY/MM/DD format.
I selected the dates in a column.
Clicked the Kutools menu followed by Format and then Apply Date Formatting.
In the Date formatting selection I clicked 2001-03-14 for YYY-MM-DD.
I do not see anything in the Preview side of the dialogue.
If I click Apply, the button is greyed out but I still do not have anything in the Preview side.
See the attachment
Does anyone have an idea why this is not working?

Any ideas?
Chip
 
2 years ago
·
#2476
Hi cheadrick,

I just check the function on my computer, it worked well. So, please tell me the version of the Kutools for Excel you are using. Also, please send me the information about your operating system ( Which version of Windows operating system am I running?  ), Excel version and the language you are using on your computer.

Thanks in advance.

Amanda
2 years ago
·
#2477
Hi,
I am using English language for my operating system and all applications.

Kutools for Excel Version 26.0 with Licensed quantity 1

from System Settings:
System type 64-bit operating system, x64-based processor
Edition 10.0.19043 Build 19043
Version 21H1
OS Build 19043.1526

from Excel desktop app:
Microsoft® Excel® for Microsoft 365 MSO (Version 2201 Build 16.0.14827.20198) 64-bit

Chip
2 years ago
·
#2478
Hi Chip,

Could you please check if the dates in the F column are formatted as text or not?

Thanks in advance.

Amanda
2 years ago
·
#2479
I changed the column Format to TEXT and tried to Apply Date Formatting.
I still do not get the dates to change to YYYY-MM-DD.
After running this command the Format is changed to DATE.
I have also tried when the column is formatted as GENERAL and get the same results.
See the Attachments.
Chip
2 years ago
·
#2480
Hi Chip,

Is it OK for you to attach the excel file and send that to me? Before you do it, please delete private information.

Thanks in advance.
Amanda
2 years ago
·
#2481
Hi Amanda,
I have stripped all the personal data so this file only contains the dates in a column.
This is the worksheet I used to create the screenshots.
Awaiting your reply,
Chip
2 years ago
·
#2482
Hi Chip,

We've just checked the data. The dates in your excel are somehow not standard dates so that Excel could not recognize them and treat them as text. This is why the Date Formatting does not work.

Here is the solution: Before you apply Date Formatting, please convert them to standard dates first. Please do as follows:

1. Select all the dates.
2. Find Ranges & Cells group under the Kutools tab.
3. Click Content and select Convert to Date.
convert to date.png

After this, you will find Apply Date Formatting working properly.

Amanda
Attachments (1)
2 years ago
·
#2483
Hi Amanda,
When I clicked Content then Convert to Date the cells were changed to YYYY-MM-DD as I wanted without having to use Apply Date Formatting. see attachment.
The dates are now in the format I need.

The original file was downloaded from an American site as a CSV file.
I opened the CSV file and saved it in XLSX format before trying to Apply Date Formatting.
Could this have caused my problem?
I would just like to know for the next time I download this file again.

Thank you,
Chip
 
2 years ago
·
#2484
The original file was downloaded from an American site as a CSV file.
I opened the CSV file and saved it in XLSX format before trying to Apply Date Formatting.
Could this have caused my problem?

Hi Chip,

Yes, this might be the reason. We were wondering you might get the file somewhere but not made it yourself since the situation is kinda weird.

So next time when you download similar files online, remember to apply the Convert to Date feature to the dates :)

Amanda
2 years ago
·
#2485
HI Amanda,
Thanks for the tip.
This request can now be CLOSED with a successful outcome.
Thank you,
Chip
  • Page :
  • 1
There are no replies made for this post yet.