Start Free Trial

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.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

Thanks.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thank you

@ProfessorJimJam's first solution seems to work. Tried it and it fixed the case where I had a leading '

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

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

please see it in the attached workbook
Book1.xlsx

ASKER

Super thank both of you.