• 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

D

Deepak Sharma

Member
Hello Everyone,
I have a list of dates in MM/DD/YYYY Format but I need to see these dates in DD/MM/YYYY Format. I tried Data(Right, Mid, Left) function but that is not changing it. Please advise how to do it. Kindly see the attachment.

Regards,
 

Attachments

  • Date Format Change.xlsx
    11.8 KB · Views: 5
How is data being brought in?

These dates are all stored as date value and displayed as "m/d/yyyy" format.

Hence, doing right/left/mid operation on it won't work. As what's actually stored is long int value and not string.

Instead of trying to manipulate date value, I'd recommend reviewing your process of how the data is brought in, and ensure data is either brought in as text or in correct format (format issue is often caused by different date format set in source and destination, and can also be caused by mismatch between system region setting and Excel's setting).
 
Hi Chihiro, Thanks for your reply.
Actually, I have exported this data from one of the software called Sales Force, from that it's coming in this format only. There are two options to export i.e. as XLS or CSV. So I exported in XLS.

Thanks!
 
Hi ,

That may not be possible , unless you deliberately make it happen.

When you change the format of your data range to General , you will see that some of the data changes to numbers , while others remain in their format which appears to be dates but which Excel does not recognize as dates.

One way is to convert all of them to text and then re-convert them to dates.

See the attached file.

Narayan
 

Attachments

  • Date Format Change.xlsx
    14.9 KB · Views: 4
Check the date/region setting in Sales Force and see if it's different from your system/Excel. That's the likely culprit for the issue.

I've experienced similar issue when dealing with Sales Force, Crystal Reports and other systems. Typically, these systems has U.S. format setting, and my PC was set to Canadian format.

I have since set my system to U.S. format, and adjust cell range format when needed to display Canadian format.
 
Thank you so much, Narayan! It's amazing you solve the issues in an instant always touchwood.
Thanks, Chihiro, Yes I would try to find & change the settings, Even I tried but couldn't find where to do it. But yes will try to find it out again.

Thanks, both of you....!!!!

Regards,
 
You must log in or register to reply here.
Back
Top