• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date Format - Not Changing

A

Amit Punia

Member
In the attached file, I'm having dates in column A. I tried changing the format of these dates from dd-mm-yy to mm-dd-yy(or any other format), however I'm unable to see the changes. I'm struggling with this kind of problem since 2012. Is there any permanent solution to this problem.

If yes then please share the solution too.

Thanks in advance.
 

Attachments

  • Date Format - Not Changing.xlsx
    10.3 KB · Views: 12
@Amt,

Please use below formula to convert date
=DATE(2000+RIGHT(A2,2),MID(A2,4,2),LEFT(A2,2)).

This happens due to incorrect date format, there is two way to resolve this problem

1) change your system date format
2) use above formula


Amit Punia said:
In the attached file, I'm having dates in column A. I tried changing the format of these dates from dd-mm-yy to mm-dd-yy(or any other format), however I'm unable to see the changes. I'm struggling with this kind of problem since 2012. Is there any permanent solution to this problem.

If yes then please share the solution too.

Thanks in advance.
Click to expand...
 
Your dates are not real dates but text looking like dates. ( you can see that as they are left-aligned instead of right-aligned)
Select the column go to the Data ribbon - select " Text to columns" and click Finish immediately.
All "text" dates now are real dates and you can use them as such
 
@xlstime - The formula you provided solves the problem. Thanx a lot buddy.

@pecoflyer - I'm using excel since 2012 and know all these tricks to change the format, however if you download the file and try this on the cell - It won't work.
Still thanks a lot for taking out time and providing me solution for this problem.
 
Dear Amit, Change your system date struture.
Go To "Start" > "Control Panel" > Open "Regional and Language Options > Select "English (United States) > Go To "Customize" > Go To "Date" > In short Date Format is "M/d/yyyy" & Long Date Format is "dddd, MMMM dd, yyyy"
 
Hi Amit,

Adding 2 more ways:
  • Enter 1 in any separate cell, say B2
  • Copy B2
  • Select your range A2:A109
  • Paste Special > Select Multiply > OK
  • Than just Format the numbers to date.

Or see this:

Dates.png

Regards,
 
i faced the same problem.you can also try this.
first select all dates then go to date-text to column.
then check fixed width box and click nextthen again click next button.
after that in next wizard check the "date" box and select DMY.
then your date will change what you want...
 
@AVK - That is something awesome. That's the permanent solution for all the dates in all the worksheets/workbooks I'm gonna work on in future.

Thanks @Khalid NGO for your inputs, it is working absolutely fine.

@RAFI - Kindly download the file and try solving it the way you're telling here. It won't work. I tried that several times. You can read above solutions by other members. That's the real solution to this problem. Still thanks for taking out time and sharing the solution with me.
 
You must log in or register to reply here.
Back
Top