EXCEL - Date Formatting

I have a spreadsheet, where one column has dates formatted in two different ways

Examples

5/16/77

OR

08/22/78

For the cells where the month has  leading zero, the dates are not grouped by year in the filter drop down. If there is no leading zero, in the filter drop down they are grouped by year.

I have tried to reformat the entire column, but this does not change.

Additionally, when I export this spreadsheet/table into excel, the dates that have the leading zero do not import and these cells are blank.

Please advise on how to reformat.
exp vgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamCommented:
you can use a helper column with function of TEXT to keep the leading zeros

for example  =text(a1,"mm/dd/yy")
1
MlandaTCommented:
If cells are showing a leading zero on '08/22/78' and since we know it's not due to a Date Format, then it's probably because the values in these cells are strings which have been captured with an apostrophe character in front. See if this is the case and remove those leading apostrophes from the cell values.
0
exp vgAuthor Commented:
I do not see a zero in front of the cells with a leading zero.

Thanks.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Start This Course
ProfessorJimJamCommented:
@Exp Vg

if my formula above does not give you correct result this use this one

this example is used where you date is in A2

=TEXT(DATEVALUE(A2),"mm/dd/yy")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
exp vgAuthor Commented:
Thank you
0
MlandaTCommented:
@ProfessorJimJam's first solution seems to work. Tried it and it fixed the case where I had a leading '
0
ProfessorJimJamCommented:
better yet use this one, it will take care of everything


=IFERROR(TEXT(DATEVALUE(A2),"mm/dd/yy"),TEXT(A2,"mm/dd/yy"))+0
0
ProfessorJimJamCommented:
please see it in the attached workbook
Book1.xlsx
0
exp vgAuthor Commented:
Super thank both of you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.