Date format problem

gregfthompson
gregfthompson used Ask the Experts™
on
The attached file contains dates. Some will format. Some will not.
I'd like to be able to format them all.
Date-problem-example.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Naresh PatelFinancial Adviser

Commented:
Try this in column B
=TEXT(A9,"DD/MM/YYYY")

Open in new window

Author

Commented:
Thanks.

This allows all cells to be formatted in the same way.
But it still will not allow the date format function to work to change the format.
I am seeking to be able to change the format through the date format function.
They are likely entered as Text format. To change to something that can be identified as a date, enter '1' in a blank cell. Copy that cell, select the date cells and Paste Special, with Multiply as the operation.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Because some are Text entries, they look like dates but not the real dates.
To convert them all to real dates, follow these steps..

Select column A --> Data Tab --> Text to columns --> Delimited --> Next --> Next --> Select Date radio button for column data format --> Finish.

Now you can apply all the date formats to the dates in column A.
Roy CoxGroup Finance Manager

Commented:
Copy a blank cell.

Select all the dataes then PasteSpecial. Scroll don and select PasteSpecial then Add
you are having problem because of regional settings, in the sheet you gave us, what is the correct date for a16? is it dec 10 or oct 12?
 
if it is OCt 12 then use this in the next cell (b16) to convert it
=IFERROR(DATEVALUE(TEXT(A16,"MM/dd/YYYY")),DATEVALUE(MID(A16,4,2) & "-" & LEFT(A16,2)&"-"&RIGHT(A16,4)))
Naresh PatelFinancial Adviser

Commented:
@arana,

i am not author but i tried and it works perfect.
=TEXT(IFERROR(DATEVALUE(TEXT(A2,"MM/dd/YYYY")),DATEVALUE(MID(A2,4,2) & "-" & LEFT(A2,2)&"-"&RIGHT(A2,4))),"DD-MMM-YY")

Open in new window


Good Comment

Author

Commented:
Thanks - works perfectly, and is simple.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome. Glad I could help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial