New Excel Forum

This forum has been moved to TeachExcel.

Ask all future questions in the new excel forum.

ExcelKey

inconsistent date formatting

Formulas, Functions, Formatting, Charts, Data Analysis, etc.
Forum rules
This forum is closed.

All new posts should be made at our new Excel Forum at TeachExcel.com.

inconsistent date formatting

Postby Sebastian42 » Sun Jan 08, 2017 5:50 pm

The sample spreadsheet is what is left after I have deleted all the other data that has no bearing on THIS question.
Cells highlit in yellow in column A viz, 3, 8,10, 11 and 17, are resistant to format painting, or any other reformatting I have tried - but it depends on the year. They look the same as all the other entries because I have edited them to be text - using the apostrophe prefix. If I remove the apostrophe e.g. in cell 11, the format becomes 07 10 16. If I drag cell 9 onto cell 10 it becomes 07/10/2017 - if I change 2017 to 2016, the format becomes as in cell 11. How can I remove this stubborn inconsistency ?
  • 0

You do not have the required permissions to view the files attached to this post.
Sebastian42
Rookie
 
Posts: 6
Joined: May 3, 2015
Reputation: 0
Excel Version: 2007

Re: inconsistent date formatting

Postby pecoflyer » Thu Jan 19, 2017 3:12 am

Depending on your regional settings try
Data - Text to columns then click Finish immediately. Should be OK
If not : Data text to columns - Next -Next - select date in Column data format and change the DMY to MDY then Finish.
Sorry for the late answer
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010


Return to General Excel Questions

Who is online

Users browsing this forum: Bing [Bot], Google [Bot] and 3 guests

cron