Avatar of exp vg
exp vg
 asked on

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.
Microsoft Excel

Avatar of undefined
Last Comment
exp vg

8/22/2022 - Mon
SOLUTION
Professor J

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Mlanda T

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
exp vg

ASKER
I do not see a zero in front of the cells with a leading zero.

Thanks.
ASKER CERTIFIED SOLUTION
Professor J

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
exp vg

ASKER
Thank you
Mlanda T

@ProfessorJimJam's first solution seems to work. Tried it and it fixed the case where I had a leading '
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Professor J

better yet use this one, it will take care of everything


=IFERROR(TEXT(DATEVALUE(A2),"mm/dd/yy"),TEXT(A2,"mm/dd/yy"))+0
Professor J

please see it in the attached workbook
Book1.xlsx
exp vg

ASKER
Super thank both of you.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.