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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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

for example  =text(a1,"mm/dd/yy")
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.
exp vgAuthor Commented:
I do not see a zero in front of the cells with a leading zero.

Thanks.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Learn More!
ProfessorJimJamMicrosoft Excel ExpertCommented:
@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")

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
MlandaTCommented:
@ProfessorJimJam's first solution seems to work. Tried it and it fixed the case where I had a leading '
ProfessorJimJamMicrosoft Excel ExpertCommented:
better yet use this one, it will take care of everything


=IFERROR(TEXT(DATEVALUE(A2),"mm/dd/yy"),TEXT(A2,"mm/dd/yy"))+0
ProfessorJimJamMicrosoft Excel ExpertCommented:
please see it in the attached workbook
Book1.xlsx
exp vgAuthor Commented:
Super thank both of you.
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.