asked on Date format problem
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
I'd like to be able to format them all.
Date-problem-example.xlsx
Microsoft Excel
Last Comment
Subodh Tiwari (Neeraj)
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Copy a blank cell.
Select all the dataes then PasteSpecial. Scroll don and select PasteSpecial then Add
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(A1
if it is OCt 12 then use this in the next cell (b16) to convert it
=IFERROR(DATEVALUE(TEXT(A1
@arana,
i am not author but i tried and it works perfect.
Good Comment
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")
Good Comment
ASKER
Thanks - works perfectly, and is simple.
You're welcome. Glad I could help.
Open in new window